Excel Agility: Pivot Tables - Beginners
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATAOP 1.5, ATATX 1.5, CPE 2.0
-
Excel expert David Ringstrom, CPA, introduces the basics of creating and using Excel pivot tables in this valuable presentation. Pivot tables enable you to easily create reports from complex data simply by using your mouse. David shows you how to initiate a pivot table from a list of data, add fields, dig deeper into the numbers, and much more. In addition, he points out pivot table traps and shares tricks to help ensure your reports are accurate.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the Microsoft 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
Topics Typically Covered:
- Adding fields to a blank pivot table to create instant reports.
- Determining which refresh commands will update a single pivot table versus all pivot tables in a workbook.
- Filtering pivot tables to show fewer columns and/or rows of data.
- Exploring the nuances of formatting numbers within pivot tables.
- Resetting all filters at once within a pivot table by way of the Clear Filters command.
- Creating a pivot table to transform lists of data into on-screen reports.
- Repositioning or removing subtotals within pivot tables.
- Contrasting sorting data within worksheets to the nuances of sorting data within pivot tables.
- Presenting the largest or smallest values in chart form by way of a Top 10 pivot chart.
Learning Objectives:
- Identify how to expand and collapse groups of data within a pivot table.
- Identify the best approach for formatting numbers within pivot tables.
- Define the default location for pivot table data when you click a checkbox for a given field.
Level: Intermediate
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None
Advance Preparation: None
- Introduction
- Please Ask Questions Today 00:01:59
- Excel Versions 00:03:52
- Ideal Data for Pivot Tables 00:04:47
- Initiating Pivot Table 00:14:50
- Pivot Table Interfaces 00:14:51
- Adding Fields to a Pivot Table 00:20:17
- Adding Another Row Field 00:23:09
- Number Formatting 00:24:46
- Renaming Fields 00:29:25
- Adding Columns to Pivot Tables 00:33:09
- Filter Columns 00:38:40
- Top 10 Filter 00:40:30
- Expanded PivotTable Filtering 00:44:30
- Pivot Table Subtotaling Quirks 00:48:31
- Tabular Form 00:50:42
- Clear Filter 00:54:39
- 4 Ways to Remove Fields 00:57:27
- Report Filter Feature 01:01:25
- Generate Multiple Pivot Tables 01:03:13
- Delete Report Filter Worksheets 01:05:29
- Pivot Table Sorting Nuances 01:08:54
- Pivot Table Sorting Nuances (cont.) 01:15:01
- Expanding/Collapsing 01:18:37
- Number Fields Treated as Text 01:21:32
- Correcting Number Fields Treated as Text 01:23:50
- Drill Down 01:26:19
- Forcing Pivot Tables to Recalculate 01:30:27
- Refreshing all Pivot Tables 01:32:55
- Auditing Pivot Tables 01:34:51
- Recommend Pivot Tables (2013+) 01:38:26
- Speaker Conclusion 01:42:45
- Conclusion 01:43:00
- .XLS 00:18:29
- .XLSX 00:18:33
- Analyze 00:14:58, 00:29:03, 00:55:03, 01:03:34
- Cell 00:05:57, 00:07:54
- Clear Filter 00:54:39, 00:55:11
- Column 00:07:57, 00::38:56
- Compact Form 01:12:24
- Compatibility Mode 00:06:39, 00:11:16
- Design 00:15:01, 01:12:19
- Drill Down 01:26:19
- Field 00:15:13, 00:20:19, 00:48:29
- Filter 00:38:42, 00:39:12, 01:01:39
- FILTER Function 00:36:10, 00:44:49
- Format 00:24:48
- Microsoft 365 00:04:15, 01:21:58
- Number Formatting 00:24:37
- Pivot Table 00:04:53, 00:07:16, 00:15:46, 00:54:59
- Refresh 01:31:25
- Report Filter 01:01:25
- Row 00:07:57, 00:20:46
- Table 01:27:02
- Table Feature 01:27:05
- Tabular Form 00:50:44
- Top 10 Filter 00:40:33
- Total Row 01:27:37
- Undo Command 00:55:25
- Workbook 00:07:00, 01:05:39
- Worksheet 00:05:57, 00:13:40, 01:05:32
.XLS: Spreadsheets compatible with Excel 2003 and earlier have a .XLS extension. Such spreadsheets can be used in Excel 2007 and later, but certain features will be disabled unless you convert the document to a newer format, such as .XLSX, .XLSM, or .XLSB.
.XLSX: A file with the. xlsx file extension is a Microsoft Excel Open XML Spreadsheet (XLSX) file created by Microsoft Excel. You can also open this format in other spreadsheet apps, such as Apple Numbers, Google Docs, and OpenOffice.
Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Clear Filter: To clear a filter for one column in a multicolumn range of cells or table, click the Filter button Applied filter icon on the heading, and then click Clear Filter from <Column Name>.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Compact Form: The default report layout for a pivot table is Compact Form, shown below. There are two Row fields -- Customer and Date.
Compatibility Mode: A compatibility mode is a software mechanism in which a software either emulates an older version of software, or mimics another operating system in order to allow older or incompatible software or files to remain compatible with the computer's newer hardware or software.
Design: The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options.
Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.
FILTER Function: The Excel FILTER function returns a range filtered on criteria you define. It can also handle multiple AND/OR criteria. array is the range or array containing the values you want filtered. include is the logical test that returns a Boolean array (TRUE/FALSE) the same height or width as the array.
Field: In a PivotTable or PivotChart, a category of data that is derived from a field in the source data. PivotTables have row, column, page, and data fields. PivotCharts have series, category, page, and data fields.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
Number Formatting: Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. To apply a number format, just select one or more cells and choose a format.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.
Report Filter: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Tabular Form: In Tabular Form, each Row field is in a separate column, as you can see in the pivot table below. There are two Row fields -- Customer and Date. The Row labels are not in a separate row.
Top 10 Filter: Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum. You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Undo Command: The Undo feature in Excel 2010 can quickly correct mistakes that you make in a worksheet. The Redo button lets you “undo the Undo.” The Undo button appears next to the Save button on the Quick Access toolbar, and it changes in response to whatever action you just took; the Redo button becomes active whenever you use Undo.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
