On Demand Webinar

Excel Agility: Pivot Tables Part 2

Webinar Details $219

  • Rated:
  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Software
  • Credit:   CPE 2.0
All Access Membership

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



  1. Introduction

  2. Excel Versions 00:00:55

  3. Initiating a Pivot Table 00:02:07

  4. Adding Fields to a Pivot Table

  5. Pivot Table Commands 00:09:08

  6. Filtering Blank Rows 00:14:05

  7. Select Blank Cells 00:20:38

  8. Fill in Missing Data 00:23:36

  9. Data Integrity Risks 00:27:24

  10. Data Integrity Risks (cont.) 00:31:48

  11. Pivot Tables with Table Feature 00:33:32

  12. Tables Eliminate Data Integrity Risk 00:37:36

  13. Remove  Table Feature from Worksheet 00:41:58

  14. Reconstructing Pivot Table Source Data 0044:48

  15. Preventing Pivot Table Drill Down 00:50:16

  16. Protecting Pivot Table Data 00:53:00

  17. Tabular Form 00:55:45

  18. Build a Pivot Table 00:59:48

  19. Pivot Table Calculations 01:02:04

  20. Calculated Fields 01:05:54

  21. GetPivotData Function 01:11:27

  22. Pivot Table Slicers (Excel 2010+) 01:17:29

  23. Timeline Feature (Excel 3013 and later) 01:22:27

  24. Creating a Pivot Chart 01:26:35

  25. Creating a Pivot Chart (cont.) 01:29:45

  26. Filtering a Pivot Chart 01:30:44

  27. Hiding Field Buttons on Pivot Chart 01:32:30

  28. Formatting Pivot Charts 01:33:46

  29. Dynamic Pivot Chart Titles 01:35:21

  30. Excel 2007 Slicer/Pivot Charts 01:39:09

  31. 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

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.

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.

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.

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.

Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.

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.

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.

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.

Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.

Design: The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options.

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.


Guest Speaker

  • David H. Ringstrom, CPA

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.