Excel Agility: Pivot Tables Part 1
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Software, Finance, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
Many users shy away from Excel’s PivotTable feature because they believe there’s a long learning curve or that specialized knowledge is required. As you’ll see in this presentation by Excel expert David Ringstrom, CPA, pivot tables allow you to instantly summarize lists of data into meaningful reports using your mouse. As with many aspects of Excel, pivot tables have several quirks that can frustrate new users, so David brings those to your attention so that you can use the feature effectively.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples used in the course.
Who Would Be Interested in This Course:
Practitioners who could benefit by learning how to use Excel pivot tables to create reports.
Your Benefits of Attending:
- Compiling unwieldy data into the format required for pivot table analysis quickly and easily.
- Using Excel’s PivotTable feature to quickly identify anomalies within QuickBooks data.
- Adding fields to pivot tables.
- Discovering how pivot tables differ from worksheet formulas and learning the importance of the Refresh command.
- Identifying the requirements of ideal data sets to be analyzed within your pivot tables.
- Avoiding frustration by understanding the nuances of pivot table formatting.
- Learning the nuances associated with subtotaling data within a pivot table.
- Creating a macro that will automatically remove the words “Sum Of” from your pivot table fields.
- Filtering pivot table data based on date ranges by way of the Timeline feature in Excel 2013 and later.
- Learning multiple ways to remove fields from a pivot table.
- Disabling the GETPIVOTDATA function if it’s not needed for your analysis.
- Understanding why numeric data may appear in a pivot table more than once and how to correct the problem.
Learning Objectives:
- Identify the best approach for formatting numbers within a pivot table.
- List multiple ways to remove fields from a pivot table.
- Recall the requirement that enables the Show Report Filter Pages command.
Level:
Basic
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
None
Advance Preparation:
None
Introduction
Excel Versions 00:01:37
Ideal Data for Pivot Tables 00:02:50
Initiating Pivot Table 00:07:30
Pivot Table Interfaces 00:09:17
Adding Fields to a Pivot Table 00:13:11
Adding Another Row Field 00:16:39
Managing Subtotal Positions 00:18:44
Number Formatting 00:20:40
Renaming Fields 00:27:29
Adding Columns to Pivot Tables 00:30:09
Filter Columns 00:33:37
Top 10 Filter 00:36:21
Pivot Table Subtotaling Quirks 00:39:03
Tabular Form 00:40:55
Clear Filter 00:44:28
4 Ways to Remove Fields 00:46:01
Report Filter Feature 00:49:41
Generate Multiple Pivot Tables 00:52:09
Delete Report Filter Worksheets 00:55:23
Pivot Table Sorting Nuances 00:57:20
Pivot Table Sorting Nuances (cont.) 01:01:54
Expanding/Collapsing 01:04:23
Insert Blank Lines 01:07:07
Number Fields Treated as Text 01:10:19
Correcting Number Fields Treated as Text 01:12:33
Counting Text Instances in a Pivot Table 01:14:47
Drill Down 01:17:16
Covert Pivot Tables 01:20:46
Forcing Pivot Tables to Recalculate 01:23:35
Refreshing all Pivot Tables 01:27:17
Auditing Pivot Tables 01:28:55
Recommend Pivot Tables (2013+) 01:33:02
XLS File 01:34:51
Convert XLS Workbooks 01:37:36
Conclusion 01:39:32
- Compact Form 00:58:13
- Drill Down 01:17:16
- Field 00:13:15
- Format Cells Dialog Box 00:24:53
- Number Formatting 00:20:40
- Pivot Table 00:02:55
- Refresh 01:27:30
- Report Filter 00:49:41
- Tabular Form 00:59:11
- Top 10 Filter 00:36:21
Compact Form: The default report layout for a pivot table is Compact Form, shown below. There are two Row fields -- Customer and Date.
Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.
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 Cells Dialog Box : The Format Cells dialog box is where you set the formatting options for your report objects. You can format the number, font, alignment, border, and pattern for a row or column header and the values.
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.
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.
