Excel Agility: Pivot Tables - Beginners
Access this expert-led webinar instantly, available anytime on-demand.
Included in All-Access Membership
During this valuable presentation, Excel expert David Ringstrom, CPA, outlines techniques for verifying the integrity of even the most complicated Excel spreadsheets. He walks you through how to: use Excel’s formula auditing and error-checking tools, identify duplicates in a list, monitor the ramifications of even minor changes made to your workbooks, verify sums and totals quickly, and more. In addition, David explains the Show Formulas feature, the Trace Precedents feature, and Excel’s Personal Macro Workbook.
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 (2021, 2019, 2016 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 2021, Excel 2019, and so on.
Topics typically covered:- Discovering four different ways to remove data from a pivot table report.
- Filtering pivot table data based on a new dimension by using the Report Filter command.
- Deleting a group of worksheets all at once from within an Excel workbook.
- Contrasting sorting data within worksheets to the nuances of sorting data within pivot tables.
- Managing information overload by collapsing or expanding pivot table fields.
- Determining the one way you can incorporate blank rows within a pivot table.
- Understanding once and for all why pivot tables sometimes count numbers within a field instead of summing.
- Using the Summarize By command to make Excel sum numbers instead of counting.
- Drilling down into the details behind any amount within a pivot table with just a double-click.
- Converting a pivot table to static numbers for archival purposes or to prevent drilling down into the underlying data.
- Determining which refresh commands in Excel update a single pivot table versus all pivot tables in a workbook.
- Auditing the data source behind pivot tables in Excel spreadsheets.
- Identify how to add, review, and print worksheet comments with ease.
- Apply Excel tools and techniques that allow you to evaluate portions of a formula or entire formulas.
- Define how to implement the Watch Window to monitor the ramifications of even minor changes to your workbooks.
Practitioners who review and audit Excel spreadsheets created by others, or those who wish to improve the integrity of their own spreadsheets.
Level: Beginning
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None
Advance Preparation: None
- Introduction
- Topics at a Glance 00:02:06
- Please Ask Questions Today 00:03:37
- Presenting with Microsoft 365 for Windows 00:05:51
- Section 1 PivotTable Fundamentals and Setup 00:07:49
- Identifying Ideal Data for PivotTables 00:08:50
- Creating a PivotTable from a Data Range 00:13:51
- Exploring PivotTable Interfaces 00:18:23
- Building a Basic PivotTable 00:20:35
- Section 2: Structuring and Formatting PivotTables 00:27:11
- Adding Columns to PivotTables 00:28:49
- Removing Fields in Four Ways 00:34:33
- Renaming PivotTable Fields 00:39:58
- Applying PivotTable Number Formatting 00:49:33
- Using Tabular Form 00:53:40
- Section 3 Filtering, Slicers, and Report Management 00:59:41
- Collapsing and Expanding a PivotTable 01:00:31
- Filtering PivotTable Rows and Columns 01:05:41
- Clearing PivotTable Filters 01:05:41
- Adding a Report Filter to a PivotTable 01:14:12
- Generating Multiple PivotTables 01:18:02
- Using PivotTable Slicers 01:23:22
- Section 4: Maintaining and Auditing Pivot Tables 01:27:32
- Refreshing PivotTables 01:28:04
- Creating a PivotTable from an Excel Table 01:32:36
- Auditing PivotTables 01:37:07
- Section 5: Accelerating Analysis with Built-In Tools 01:38:04
- Using Recommended PivotTables 01:38:24
- Using Excel’s Analyze Data Feature (Microsoft 365) 01:38:58
- What We Covered 01:42:14
- Now It’s Your Turn—But I’m Here If You Need Me! 01:43:17
- Presentation Closing 01:43:49
-
David H. Ringstrom, CPA
David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]
CPE Credit
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 [email protected].
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.
ATATX Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.ATAOP Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.ATAAA Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.IAAP Credit
Aurora Training Advantage has reviewed the content of this program and believes it aligns with the CAP Body of Knowledge. We estimate this program may qualify for 1 recertification point(s), based on 1 point per 1 hour of eligible training. Final determination of applicability rests with the CAP designee, who is responsible for documenting alignment for recertification purposes.
- .XLS 00:10:56
- Analyze 01:01:11
- Analyze Data 01:38:22
- Artificial Intelligence (AI) 00:03:19, 00:09:10, 01:39:03
- Cell 00:11:40, 00:49:51, 01:24:05, 01:39:11
- Columns 00:02:52, 00:09:33, 00:11:45, 00:27:19, 00:34:48, 00:59:58, 01:06:54, 01:15:04, 01:24:35
- Compact Form 00:31:01
- Compatibility Mode 00:10:21
- Dialog Box 00:15:05, 00:50:06, 01:08:09, 01:24:16, 01:38:37
- Drill Down
- Field 00:16:50, 00:21:29, 00:28:40, 00:35:58, 00:50:51, 01:17:58
- Filter 00:02:46, 00:17:07, 00:59:57, 01:05:26, 01:15:05
- Formula 00:01:47, 01:28:24
- Microsoft 365 00:05:49, 00:48:49
- Number Formatting 00:49:35, 00:53:31
- Pivot Table 00:02:18, 00:08:01, 00:11:16, 00:14:16, 00:21:57, 00:34:54, 00:40:33, 00:48:20, 00:59:49, 01:24:07, 01:27:40, 01:32:34, 01:37:37
- Refresh 01:27:46, 01:38:50
- Ribbon 00:17:34
- Row 00:02:52, 00:09:38, 00:11:45, 00:21:30, 00:28:32, 00:34:48, 00:59:58, 01:06:44, 01:14:39, 01:37:17
- Slicer 00:03:02, 00:11:27, 01:00:26, 01:23:43
- Spreadsheets 00:00:0, 00:09:23, 00:15:32
- Table 00:09:57, 01:28:01
- Tabular Form 00:28:23
- Task Pane 00:08:27, 01:00:00
- Top 10 Filter
- Top 10 Filter 01:05:41
- Workbook 00:10:27
- Worksheet 00:15:17, 01:19:08
.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.
Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.
Analyze Data: Analyze Data in Excel empowers you to understand your data through natural language queries that allow you to ask questions about your data without having to write complicated formulas. In addition, Analyze Data provides high-level visual summaries, trends, and patterns.
Artificial Intelligence (AI): Artificial intelligence is intelligence demonstrated by machines, as opposed to the natural intelligence displayed by humans or animals.
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.
Compact Form: The default report layout for a pivot table is Compact Form, shown below. There are two Row fields -- Customer and Date.
Compatibility Mode: A compatibility mode is a software mechanism in which a software either emulates an older version of software, or mimics another operating system in order to allow older or incompatible software or files to remain compatible with the computer's newer hardware or software.
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.
Field: In a PivotTable or PivotChart, a category of data that is derived from a field in the source data. PivotTables have row, column, page, and data fields. PivotCharts have series, category, page, and data fields.
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.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Formula: A formula is an expression which calculates the value of a cell.
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.
Number Formatting: Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. To apply a number format, just select one or more cells and choose a format.
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.
Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
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
Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
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.
Top 10 Filter: Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum. You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
This webinar received a total of 8 survey responses. Attendees have given an average rating of 4.6 stars out of a possible 5, reflecting the quality and value of the content presented.
Reviews From Webinar Survey
Our webinars are crafted to deliver exceptional value and insight to business professionals. Below, you'll find genuine feedback from attendees.