Excel Accountant: Pivot Tables for Data Analysis

Learning Objectives

• To identify the location of the pivot table-related Subtotals command within Excel's ribbon menu interface.
• To describe the indicator that signifies whether the columns or rows of a pivot table have been filtered.
• To identify the location of the Field List command within Excel's ribbon menu interface.
• To recall which menus appear and disappear as you click within or outside of a pivot table.
• To state the mouse action that enables you to reconstruct the underlying pivot table source data.
• To recall how to enable or disable the GETPIVOTTABLE feature in Microsoft Excel.

Course Description

In this CPE session Excel expert David H. Ringstrom, CPA, helps you uncover the feature that Microsoft reports 80% of Excel users have yet to try. As you’ll see, pivot tables are an easy-to-use report-writing tool that instantly summarizes lists of data with just a few actions with your mouse. Of course, when it comes to Excel there can be nuances that complicate things, which David bring these to your attention so that you can work effectively with pivot tables. This presentation will empower you to take your pivot table knowledge to the next level. David takes you beyond the basics of pivot tables by demonstrating how pivot tables differ from worksheet formulas, the importance of the Refresh and Report Filter commands, how to disable the GETPIVOTDATA function, how to drill down into numbers with a simple double-click, and much more. You'll also see how to quickly transform unwieldy reports into pivot table-ready lists.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 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. 

Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on. 

This Online Excel CPE webinar covers the following Key Topics: 

• Repositioning or removing subtotals within pivot tables.
• Employing pivot tables to count the number of times an item appears in a list.
• Repositioning or removing subtotals within pivot tables.
• Contrasting sorting data within worksheets to the nuances of sorting data within pivot tables.
• Determining which refresh commands in Excel update a single pivot table versus all pivot tables in a workbook.
• Adding fields to a blank pivot table to create instant reports.
• Creating a pivot table to transform lists of data into on-screen reports.
• Jump-starting pivot tables by way of the Recommended Pivot Tables feature in Excel 2013 and later.
• Understanding the nuances of formatting numbers within a pivot table.
• Deleting a group of worksheets all at once from within an Excel workbook.
• Utilizing the Tabular Format command to display pivot table data in two or more columns instead of a single column in Compact Form.
• Presenting the largest or smallest values in chart form by way of a Top 10 pivot chart.
• Creating a pivot table to transform lists of data into on-screen reports.
• Understanding the data integrity risks posed by pivot tables when users add additional data to the original source list.
• Utilizing the Tabular Format command to display pivot table data in two or more columns instead of a single column in Compact Form.
• Understanding the nuance of crafting formulas that reference data within pivot tables.
• Adding a percentage column to a pivot table with just a couple of mouse actions.
• Visualizing lists of data graphically by way of Excel’s PivotChart feature.
• Drilling down into the details behind any amount within a pivot table with just a double-click.
• Developing calculated fields that perform math on data within the source data.
• Removing the Table feature from Excel spreadsheets once it’s no longer needed or simply erasing the alternate row shading.
• Visualizing lists of data graphically by way of Excel’s PivotChart feature.
• Understanding how pivot chart formatting works much like formatting other types of charts in Excel.

Overview

• Ideal Data for Pivot Tables

  3 mins
• Adding Columns to Pivot Tables

  31 mins
• Generate Multiple Pivot Tables

  53 mins
• Convert Pivot Table to Values

  81 mins
• Adding Fields to a Pivot Table

  106 mins
• Pivot Tables with Table Feature

  134 mins
• Hiding Field Buttons on Pivot Chart

  193 mins