On Demand Webinar
Webinar Details $219
- Webinar Length: 525500 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
In Part 2, Excel expert David Ringstrom, CPA, goes beyond the basics of pivot tables. He explains the PivotTable feature, the PowerPivot feature, the Recommended PivotTables feature, and others. After participating in David’s presentation, you’ll know how to create self-updating titles for charts and pivot charts, expand and collapse pivot table elements, drill down into numbers with a simple double-click, and more.
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 would like to learn how to manipulate their pivot table data faster and more efficiently.
Your Benefits of Attending:
- Avoiding disabled features by converting Excel 97–2003 files to modern workbook formats with ease.
- Discovering the Recommended PivotTables feature.
- Using a simple keyboard shortcut to post the same formula to multiple cells at once.
- Understanding why numeric data may appear in a pivot table more than once and how to correct the problem.
- Learning how to utilize the PowerPivot feature in Excel 2010 and later.
- Understanding why pivot tables sometimes display amounts as text or count amounts instead of summing.
- Seeing multiple ways to remove fields from a pivot table.
- Learning how to expand and collapse pivot table elements, thereby avoiding information overload.
- Compiling unwieldy data into the format required for pivot table analysis quickly and easily.
- Identifying the requirements of ideal data sets to be analyzed within your pivot tables.
- Staving off frustration by filling blank cells within any columns that contain numbers with zeros before you create pivot tables.
- Learning the nuances associated with subtotaling data within a pivot table.
Learning Objectives:
- Identify how to quickly transform lists of raw data into usable reports in just a few simple steps.
- Recall how the Table feature can improve the integrity of pivot tables.
- Recall how to compare calculation methods within pivot tables.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Previous Experience with Excel Pivot Tables
Advance Preparation:
None
Introduction
Excel Versions 00:00:55
Initiating a Pivot Table 00:02:07
Adding Fields to a Pivot Table
Pivot Table Commands 00:09:08
Filtering Blank Rows 00:14:05
Select Blank Cells 00:20:38
Fill in Missing Data 00:23:36
Data Integrity Risks 00:27:24
Data Integrity Risks (cont.) 00:31:48
Pivot Tables with Table Feature 00:33:32
Tables Eliminate Data Integrity Risk 00:37:36
Remove Table Feature from Worksheet 00:41:58
Reconstructing Pivot Table Source Data 0044:48
Preventing Pivot Table Drill Down 00:50:16
Protecting Pivot Table Data 00:53:00
Tabular Form 00:55:45
Build a Pivot Table 00:59:48
Pivot Table Calculations 01:02:04
Calculated Fields 01:05:54
GetPivotData Function 01:11:27
Pivot Table Slicers (Excel 2010+) 01:17:29
Timeline Feature (Excel 3013 and later) 01:22:27
Creating a Pivot Chart 01:26:35
Creating a Pivot Chart (cont.) 01:29:45
Filtering a Pivot Chart 01:30:44
Hiding Field Buttons on Pivot Chart 01:32:30
Formatting Pivot Charts 01:33:46
Dynamic Pivot Chart Titles 01:35:21
Excel 2007 Slicer/Pivot Charts 01:39:09
Conclusion 01:40:23
- Analyze 00:09:44
- Design 00:09:44
- Drill Down 00:44:52, 00:50:18
- Filter 00:18:38
- Go To Special Command 00:21:11
- Pivot Chart 01:26:35
- Pivot Table 00:02:16
- Pivot Table Tools Menu 00:09:35
- Slicer Feature 01:17:50
- Table Feature 00:33:35
- Tabular Form 00:55:45, 00:56:07
Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.
Ctrl-Enter: When you have two or more cells selected in Excel, this keyboard shortcut puts the contents of the first cell in the selection in all selected cells at once.
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: 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.
GETPIVOTDATA: This feature allows users to craft formulas that refer to specific data within the pivot table, as opposed to a specific cell address, so that formulas maintain integrity even if the pivot table fields are rearranged. This feature can be enabled or disabled as needed.
Go To Special Command: Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet.
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.
PivotTable Tools Menu: In the worksheet containing a PivotTable, the Ribbon will contain the PivotTable Tools, with ANALYZE and DESIGN Tabs. The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable. The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options.
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
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.
