On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Software, Taxation and Accounting
- Credit: ATAOP 1.5, ATATX 1.5, CPE 2.0
-
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.
Your Benefits For Attending
- 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.
- Introduction
- Please Ask Questions Today 00:02:57
- Excel Versions 00:05:05
- Grouping Transactions By Month/Quarter/Year 00:06:41
- Subtotal by Year 00:19:59
- Creating a PivotChart Steps 1 - 6 00:21:26
- Creating a PivotChart Steps 7 - 8 00:25:03
- Filtering a PivotChart 00:32:37
- Dynamic PivotChart Titles 00:35:37
- PivotTable Conflicts 00:40:30
- Pasting Linked Pictures of Pivot Tables 00:47:04
- UNIQUE Function with Tables (Excel 2021+) 00:53:04
- SORT/UNIQUE (Excel 2021+) 00:58:32
- SUMIF with Spilled Range Operator 01:01:09
- Creating Custom Lists 01:09:27
- Custom Lists Within PivotTables 01:14:07
- Disabling Custom List Sorting 01:15:44
- Expanded PivotTable Filtering 01:21:45
- Create a PivotTable Clean-Up Macro Steps 1 -13 01:24:41
- Create a PivotTable Clean-Up Macro Steps 13 - 20 01:26:59
- PivotTable Clean-Up Macro Programming Code 01:32:17
- Testing the PivotTable Clean-Up Macro 01:34:59
- PivotTable Macro Icon 01:35:59
- Enabling PowerPivot in Excel 01:40:00
- Creating PivotTables with PowerPivot 01:42:44
- PowerPivot Scheduled Refreshing 01:43:39
- Thank You For Attending! 01:46:01
- Presentation Closing 01:46:43
- Add-in 01:40:49
- Analyze 00:09:34
- Cell 00:11:43, 00:22:26, 00:47:22, 01:10:48, 01:15:47, 01:35:01
- Column 00:47:40, 00:57:54, 01:24:26
- Compact Form 01:17:56
- Custom Lists 00:02:51, 01:09:34, 01:14:22
- DATEVALUE 00:09:47
- Dialog Box 00:11:39, 01:16:50, 01:27:52
- Dynamic Array 00:01:41, 01:01:38, 01:45:57
- Filter 00:32:51, 00:34:45, 01:15:57, 01:21:48
- Formula 00:01:47, 00:14:52, 00:35:34, 00:53:37, 01:00:02
- Linked Picture 00:47:06
- Macro 00:02:42, 01:24:41, 01:31:06, 01:38:06
- Macro Recorder 01:27:06
- Microsoft 365 00:01:37, 00:05:15, 00:23:29
- Personal Macro Workbook 01:28:05
- Pivot Table 00:01:46, 00:02:26, 00:09:31, 00:20:22, 00:22:39, 00:36:50, 00:53:10, 01:00:15, 01:09:21, 01:16:44, 01:21:01, 01:38:08
- Power Pivot 00:02:05, 01:41:03
- Quick Access Toolbar 01:36:17
- Refresh 01:02:10, 01:20:50
- Ribbon 01:40:17
- Scroll Bar 01:12:10
- Slicer 00:45:57
- Slicer Feature 00:40:53
- SORT 00:58:32, 01:05:36, 01:16:27
- SUBTOTAL 00:20:07
- SUMIF 01:01:22, 01:04:13
- Table 00:11:37, 00:53:35, 01:43:10
- Tabular Form 01:17:46
- TEXT Function 00:14:58
- UNIQUE 00:53:04
- Visual Basic Editor 01:31:02, 01:34:00
- VSTACK Function 01:07:32
- Worksheet 00:01:39, 00:10:32, 00:27:00, 00:47:29, 01:43:50
Add-In: An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
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.
DATEVALUE: The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations. To view a date serial number as a date, you must apply a date format to the cell.
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.
Dynamic Array Function: Dynamic Arrays will make certain formulas much easier to write. You can now filter matching data, sort, and extract unique values easily with formulas. Dynamic Array formulas can be chained (nested) to do things like filter and sort. Formulas that return more than one value will automatically spill.
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.
Formula: A formula is an expression which calculates the value of a cell.
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 Pivot: Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
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.
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.
SORT: Sorting is the process of arranging objects in a certain sequence or order according to specific rules. In spreadsheet programs such as Excel and Google Spreadsheets, there are several different sort orders available depending on the type of data you're sorting.
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
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.
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.
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.
UNIQUE: =UNIQUE - The Excel UNIQUE function returns a list of unique values in a list or range.
VSTACK Function: VSTACK returns the array formed by appending each of the array arguments in a row-wise fashion. The resulting array will be the following dimensions: Rows: the combined count of all the rows from each of the array arguments. Columns: The maximum of the column count from each of the array arguments.
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.
