On Demand Webinar
Webinar Details $219
- 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
-
Excel expert David Ringstrom, CPA, shows you step-by-step how to push the boundaries of pivot tables and add even more interactivity to them by grouping data in various ways. In this comprehensive course, David 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 much 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:
- Converting .XLS files compatible with Excel 2003 into the modern Excel workbook format.
- Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges.
- Summarizing data from Access databases with pivot tables, even if you don’t have Microsoft Access installed.
- Altering the default sort order within pivot tables to a hierarchy of your choice with Custom Lists.
- Displaying two or more pivot tables close together on a single worksheet without triggering a conflict.
- Filtering two or more pivot tables simultaneously by way of the Slicer feature in Excel 2010 and later.
- Filtering data within pivot tables in Excel 2010 and later by way of the Slicer feature.
- Discovering the Custom Lists feature in Excel, which enables you to embed frequently used lists into Excel’s Options dialog box for use with any spreadsheet.
- Understanding the conflicts that can arise when you position two or more pivot tables too close in proximity to each other.
- Resolving situations where data appears more than once within a pivot table.
Learning Objectives:
- Identify pivot table data in new ways by grouping based on dates or custom arrangements that you define.
- 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.
Level:
Intermediate
Format:
On-Demand Webcast
Instructional Method:
Self-Study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Intermediate-Level Experience with Excel Pivot Tables
Advance Preparation:
None
Introduction
Excel Versions 00:00:48
Summarize Access Data with Pivot Tables 00:01:36
Automatic Pivot Table Refresh 00:10:05
Creating Custom Lists 00:15:00
Custom Lists within Pivot Tables 00:21:05
Disabling Custom List Sorting 00:24:44
Autofit Column Width 00:28:49
Pivot Table Slicers (Excel 2010+) 00:32:24
Slicing Multiple Pivot Tables 00:36:50
Timeline Feature (Excel 2013+) 00:42:23
Pivot Table Conflicts 00:46:36
Pasting Linked Pictures of Pivot Tables 00:51:21
Another Pivot Table XLS Limitation 00:55:14
Convert XLS Workbooks 00:58:54
Resolving Pivot Table Duplicate Values 01:00:35
Resolving Pivot Table Duplicate Values (cont.) 01:02:29
Automating Pivot Table Number Formatting 01:04:54
Create a Pivot Table Number Format Macro 01:09:00
Automated Pivot Table Number Formatting 01:10:35
Manually Launch Number Format Macro 01:13:13
Pivot Table Macro Icom 00:14:55
Blank Cell within Data 01:16:52
Fill Blank Cells with Zeros for Pivot Tables 01:19:56
Create a Pivot Table Clean-Up Macro 01:22:07
Create a Pivot Table Clean-Up Macro (cont.) 01:26:52
Pivot Table Clean-Up Macro Programming Code 01:27:51
Testing the Pivot Table Clean-Up Macro 00:28:55
Grouping Transactions by Month/Quarter/Year 01:30:47
Group Pivot Table Columns by Fiscal Year 01:36:19
Power Pivot Table (2010+) 01:39:10
- Pivot Table 00:06:26, 00:21:09
- Refresh 00:12:35, 00:13:28
- Custom Lists 00:15:07
- Custom List Dialog Box 00:18:02
- Create Pivot Table Dialog Box 00:21:25
- Slicer Feature 00:30:44. 00:32:36
- Linked Picture 00:47:25
- Macro 01:05:15
- Macro Recorder 01:05:59
- Personal Macro Workbook 01:07:00, 01:08:24
- Visual Basic Editor 01:09:16, 01:22:06
.XLS: Spreadsheets compatible with Excel 2003 and earlier have a .XLS extension. Such spreadsheets can be used in Excel 2007 and later, but certain features will be disabled unless you convert the document to a newer format, such as .XLSX, .XLSM, or .XLSB.
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.
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.
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.
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.
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
Timeline: A visual filtering feature in Excel 2013 that enables you to filter data based on varying date ranges.
Visual Basic Editor : Excel’s development interface that can be used to edit and create macros, user forms, class modules, custom worksheet functions, and other enhancements to Excel’s functionality. The programming interface for Microsoft Excel that can be accessed by way of the Visual Basic command on Excel’s Developer tab or by pressing Alt-F11.
