Log in Sign Up
1821 Q St, Sacramento,
CA 95811, United States
support@s2revent.com
0
Your cart is empty!
Log in Sign up
Home
Excel Agility: Pivot Tables - Advanced

Excel Agility: Pivot Tables - Advanced

Human Resources
S2R2024179
100 Minutes
Friday, January 26, 2024 - Thursday, February 29, 2024
$249.00
$249.00
$349.00
$549.00

Description

Overview of the webinar

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.

Table of Contents :

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

Index :

  • XLSB 01:17:15
  • Analyze 00:13:19, 00:27:54, 00:44:55, 00:54:47
  • Cell  01:11:23, 01:37:39
  • CELL Function 00:30:52
  • Column 00:28:48, 00:30:59, 01:02:21, 01:06:16, 01:07:59, 01:11:48, 01:37:38, 01:42:21
  • Compact Form 01:07:54
  • Custom Lists 00:17:24, 00:18:10, 00:19:16, 00:21:29, 00:28:15, 01:14:02
  • Dialog Box 00:06:29, 00:13:57, 00:20:44, 01:13:38, 01:24:47
  • Fill Handle 00:18:04
  • Filter 00:27:16, 00:29:21, 00:44:01
  • ISNUMBER 01:01:50
  • Keyboard Shortcut 01:25:01, 01:26:24
  • Linked Picture 00:52:48, 00:55:41
  • Macro 01:10:19, 01:13:30, 01:18:25, 01:20:29, 01:26:27, 01:33:35, 01:40:13
  • Macro Recorder 01:13:06
  • Microsoft 365 00:03:47
  • Number Formatting 01:10:14, 01:12:35
  • Personal Macro Workbook 01:13:47, 01:18:18
  • Pivot Table 00:01:36, 00:07:41, 00:09:43, 00:13:05, 00:14:43, 00:22:59, 00:29:31, 00:39:57, 00:47:30, 00:53:09, 00:59:20,  01:06:13, 01:11:19, 01:32:09
  • Power Query 00:05:24
  • Query 00:06:24, 00:13:57
  • Quick Access Toolbar 01:26:12, 01:40:14
  • Refresh 00:13:31, 00:15:05, 01:03:06
  • Scroll Bar 00:20:28
  • Slicer 00:28:59, 00:33:51, 00:39:48, 00:41:33
  • Tabular Form 01:06:31, 01:08:02
  • Text to Columns Wizard 01:02:57
  • Visual Basic Editor 01:18:47
  • Worksheet 00:18:00, 00:47:40

Key Terms :

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 Function: The CELL function is a built-in function in Excel that is categorized as an Information Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the CELL function can be entered as part of a formula in a cell of a worksheet.

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.

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.

ISNUMBER : Use the ISNUMBER function to check if a value is a number. ISNUMBER will return TRUE when value is numeric and FALSE when not.

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.

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.

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.

Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.

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

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.

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.

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.

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

Speaker

https://s2revent.com/uploads/speaker/1815847703.jpg
David H Ringstrom
David H. Ringstrom, CPA, is a nationally recognized Microsoft Excel expert. He is the president and owner of Accounting Advisors, Inc. based in Atlanta, Georgia. David founded Accounting Advisors in 1991 as a consulting-services business, later he began teaching continuing education classes as well. His mission since is to offer quality training and consulting services on Microsoft Excel via live webcasts, on-demand self-study webcasts, and in-house engagements. David has taught hundreds of webinars on Excel and other topics, in addition to speaking at conferences and in-house engagements. More than 24 providers, located throughout the country as well as overseas, now look to David for their Excel and accounting software training needs. More than 24 providers, located throughout the country as well as overseas, now look to David for their Excel and accounting software training needs.

David’s Excel courses cover the gamut of the software’s features and functions to provide CPAs as well as accounting and financial professionals the knowledge they need to work more efficiently and effectively in Excel. David is known for saying, “Either you work Excel, or it works you.” Based on this belief, he focuses on teaching users what they don’t know but should know about Excel.

His comprehensive yet easy to understand presentations cover Excel 2019, 2016, 2013, and 2010. David’s webcasts are fast-paced, and he welcomes attendees’ questions. In addition, his detailed handouts and slides serve as handy reference tools students can fall back on after participating in his webcasts or taking his self-study courses.