Excel Agility: Pivot Tables - Advanced

On Demand Webinar

Webinar Details $219

  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Software, Taxation and Accounting
  • Credit:   CPE 2.0

All Access Membership
When you participate in this comprehensive webcast, you’ll discover how to push the boundaries of pivot tables and add even more interactivity to them by grouping data in various ways. Excel expert David Ringstrom, CPA, explains how to easily extract data from Microsoft Access, create simple macros that can resolve the most frustrating aspects of pivot tables, determine the number of duplicates in a list, reap the benefits of Excel’s Slicer feature, sort data in any order you desire, and more.

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

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

Who should attend:
Practitioners who would like to expand their knowledge of pivot tables so they can manipulate their data faster and more efficiently.

Topics covered:

• Altering the default sort order within pivot tables to a hierarchy of your choice with Custom Lists.
• Determining which refresh commands in Excel update a single pivot table versus all pivot tables in a workbook.
• Displaying two or more pivot tables close together on a single worksheet without triggering a conflict.
• Filtering data within pivot tables in Excel 2010 and later by way of the Slicer feature.
• Filtering two or more pivot tables simultaneously by way of the Slicer feature in Excel 2010 and later.
• Preventing pivot tables from automatically resizing columns when you refresh or filter the data.
• Resolving situations where data appears more than once within a pivot table.
• Summarizing data from Access databases with pivot tables, even if you don’t have Microsoft Access installed.
• Understanding the conflicts that can arise when you position two or more pivot tables too close in proximity to each other.
• Utilizing the Tabular Format command to display pivot table data in two or more columns instead of a single column in Compact Form.
• Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges.

Learning objectives:

• Recall how macros can help you format pivot table data faster and more efficiently.
• Define how to create pivot tables from information you extract from databases.
• Identify how to summarize pivot table data in new ways by grouping based on dates or custom arrangements that you define.

  1. Introduction
  2. Please Ask Questions Today 00:01:53
  3. Excel Versions 00:03:45
  4. Summarize Access Data with Pivot Tables 00:04:24
  5. Automatic Pivot Table Refresh 00:10:54
  6. Creating Custom Lists 00:19:17
  7. Custom Lists within Pivot Tables 00:23:54
  8. Disabling Custom List Sorting 00:27:29
  9. Autofit Column Width 00:41:43, 00:43:52
  10. Pivot Table Slicers (Excel 2010+) 00:41:52
  11. Slicing Multiple Pivot Tables 00:46:47
  12. Timeline Feature (Excel 2013+) 00:47:43
  13. Pivot Table Conflicts 00:51:36
  14. Pasting Linked Pictures of Pivot Tables 00:55:35
  15. Resolving Pivot Table Duplicate Values 01:00:05
  16. Resolving Pivot Table Duplicate Values (Cont.) 01:02:16
  17. Presenting Pivot Table Data in Columns 01:06:09
  18. Presenting Pivot Table Data in Columns Cont’d 01:08:04
  19. Automating Pivot Table Number Formatting 01:08:48
  20. Create a Pivot Table Number Format Macro 01:14:05, 01:15:36
  21. Automated Pivot Table Number Formatting 01:16:08
  22. Manually Launch Number Format Macro 01:17:55
  23. Pivot Table Macro Icon 01:20:01
  24. Create a Pivot Table Clean-Up Macro 01:23:06
  25. Create a Pivot Table Clean-Up Macro (Cont.) 01:24:44
  26. Pivot Table Clean-Up Macro Programming Code 01:27:02
  27. Testing the Pivot Table Clean-Up Macro 01:30:32
  28. Grouping Transactions by Month/Quarter/Year 01:38:43
  29. Subtotal by Year 01:39:37
  30. Group Pivot Table Columns by Fiscal Year 01:39:49
  31. Thank You For Attending! 01:42:45
  32. Presentation Closing 01:43:00
  • .XLSB 01:16:26
  • Analyze 00:13:34, 00:48:07
  • Cell 00:55:51
  • Column 00:42:28, 00:44:05, 01:06:16
  • Custom Lists 00:19:25, 00:19:58
  • Dialog Box 00:11:31
  • Fill Handle 00:19:53
  • Filter 00:16:34
  • Keyboard Shortcut 01:12:01
  • Linked Picture 00:55:34, 00:59:43
  • Macro 01:09:07, 01:14:12, 01:20:16, 01:30:41
  • Macro Recorder 01:09:14
  • Microsoft 365 00:03:45
  • Personal Macro Workbook 01:10:16, 01:23:41
  • Pivot Table 00:01:35, 00:04:54, 00:09:50, 00:11:01, 00:18:32, 00:37:57, 00:51:43, 01:08:51
  • Power Query 01:38:32
  • Query 00:05:39, 00:11:22
  • Refresh 00:10:26, 00:11:02, 00:52:02
  • Scroll Bar 00:22:11
  • Slicer 00:41:52, 00:43:19, 00:46:16
  • Text to Columns Wizard 01:02:26
  • Worksheet 00:05:46, 00:57:50

.XLSB: A file with the XLSB file extension is an Excel Binary Workbook file. They store information in binary format instead of XML like with most other Excel files (like XLSX). Since XLSB files are binary, they can be read from and written to much faster, making them extremely useful for very large spreadsheets.

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.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Custom Lists: The Custom Lists feature in Excel enables you to store frequenly used lists in Excel for use in any spreadsheet by simply typing one of the items on the list and then dragging the fill handle down or to the right.

Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.

Fill Handle: The little notch in the bottom right-hand corner of the selected cell or block of cells. You can drag the fill handle to copy the contents to other cells, double-click to copy contents down a column, or right-drag to reveal a hidden context menu.

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.

Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.

Linked Pictures: Provides a live snapshot of a range of cells to you in an image. You can move the image, resize it, position it wherever you want and when the source cells change, the picture gets updated, immediately.

Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.

Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.

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.

Personal Macro Workbook: A hidden workbook that typically serves as a repository for macros you wish to always be available in any Excel workbook you have open.

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.

Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.

Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.

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.

Scroll Bar: A vertical or horizontal bar commonly located on the far right or bottom of a window that allows you to move the window viewing area up, down, left, or right. Scrolls through a range of values when you click the scroll arrows or when you drag the scroll box. You can move through a page (a preset interval) of values by clicking the region between the scroll box and either scroll arrow.

Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click

Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

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.

Guest Speaker

  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees use [...]

    View Full Profile

Webinar Survey Overall Rating

This webinar received a total of 0 survey responses. Attendees have given an average rating of 0 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

0.0 / 5
Webinar Presentation
How many of the objectives of the event were met?
0.0 Stars
How useful was the information presented at this event?
0.0 Stars
Overall, how satisfied were you with this event?
0.0 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
0.0 Stars
How closely did the presenter follow the schedule?
0.0 Stars