Live Webinar
Webinar Details $219
- Webinar Date: October 4, 2024
- Webinar Time: 12:00pm - 1:40pm EDT live
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATATX 1.5, ATAOP 1.5, ATAPU 1.5, CPE 2.0
-
-
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.
Who should attend:
Practitioners who review and audit Excel spreadsheets created by others, or those who wish to improve the integrity of their own spreadsheets.
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.
Your Benefits For Attending:
- 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.
Level: Intermediate
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None
Advance Preparation: None
- Introduction
- Please Ask Questions Today 00:04:01
- Excel Versions 00:06:29
- Ideal Data for Pivot Tables 00:07:31
- Initiating Pivot Table 00:15:55
- Pivot Table Interfaces 00:21:19
- Adding Fields to a Pivot Table 00:31:04
- Adding Another Row Field 00:34:28
- Number Formatting 00:37:13
- Renaming Fields 00:41:17
- Adding Columns to Pivot Tables 00:44:08
- Filter Columns 00:48:06
- Top 10 Filter 00:49:59
- Pivot Table Subtotaling Quirks 00:54:11
- Tabular Form 00:56:09
- Clear Filter 00:59:28
- Expanding/Collapsing 01:01:21
- Drill Down 01:06:06
- Report Filter Feature 01:10:18
- Generate Multiple Pivot Tables 01:24:57
- Delete Report Filter Worksheets 01:29:16
- Pivot Table Sorting Nuances 01:29:57
- Pivot Table Sorting Nuances (Cont.) 01:32:28
- 4 Ways to Remove Fields 01:33:25
- Number Fields Treated as Text 01:34:08
- Correcting Number Fields Treated as Text 01:37:24
- Forcing Pivot Tables to Recalculate 01:37:23
- Refreshing All PivotTables 01:38:54
- Auditing PivotTables 01:39:20
- Recommended Pivot Tables Speaker 01:43:41
- Thank You For Attending! 01:45:43
- Presentation Conclusion 01:46:14
- .XLS 00:10:36
- Analyze 00:21:02, 00:59:36, 01:00:27, 01:03:50, 01:30:58
- Cell 00:08:41, 00:09:51, 00:12:43, 00:15:54, 00:20:25, 00:37:36
- Clear Filter 00:59:41, 01:00:31
- Column 00:03:43, 00:08:32, 00:22:17, 00:48:24, 00:51:26, 01:33:44
- Compact Form 01:01:45, 01:02:01
- Compatibility Mode 00:11:51
- CONCATENATE Function 00:26:05
- Design 00:56:26
- Dialog Box 00:17:16
- Drill Down 01:06:20, 01:10:00, 01:13:19
- Field 00:10:05, 00:21:28, 00:35:24, 01:33:58
- Filter 00:32:37, 00:48:11, 00:50:02, 01:10:26
- Filter Function 00:48:46
- Format 00:11:42, 00:12:03, 00:16:29, 00:20:16, 00:37:39
- Formula 00:23:27, 00:26:48, 00:34:53, 01:33:27
- Insert 00:16:36, 00:17:08
- Merge Cell 00:21:47
- Microsoft 365 00:06:51
- Number Formatting 00:37:51
- Pivot Table 00:00:09, 00:02:11, 00:06:56, 00:09:53, 00:11:21, 00:13:13, 00:17:31, 00:20:19, 00:31:32, 00:37:39, 00:41:24, 00:49:50, 01:06:19, 01:30:07
- Power Query 00:03:30
- Refresh 01:39:14
- Ribbon 00:20:36
- Row 00:03:42, 00:09:01, 00:21:38, 00:26:33, 00:31:16, 00:34:36, 00:49:16, 01:33:45
- Table 01:06:37, 01:09:40, 01:30:16
- Table Feature 01:09:25
- Tabular Form 00:56:32, 00:57:37, 01:01:36, 01:02:01
- TEXTJOIN Function 00:26:23
- Top 10 Filter 00:49:59
- Total Row 01:07:06
- Undo Command 01:00:46
- Workbook 00:10:35
- Worksheet 00:18:26, 00:29:27, 00:35:32, 01:11:30, 01:25:57
.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.
CONCATENATE Function : The CONCATENATE function in Excel is designed to join different pieces of text together or combine values from several cells into one cell.
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.
Clear Filter: To clear a filter for one column in a multicolumn range of cells or table, click the Filter button Applied filter icon on the heading, and then click Clear Filter from <Column Name>.
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.
Design: The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options.
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.
Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.
FILTER Function: The Excel FILTER function returns a range filtered on criteria you define. It can also handle multiple AND/OR criteria. array is the range or array containing the values you want filtered. include is the logical test that returns a Boolean array (TRUE/FALSE) the same height or width as the array.
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.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
Insert: When you click the Insert Function button, Excel displays the Insert Function dialog box. You can then use its options to find and select the function that you want to use and to define the argument or arguments that the function requires in order to perform its calculation.
Merge Cell: Merge cell is a function in database software that allows multiple adjacent cells to be combined into a single larger cell. This is done by selecting all cells to be merged and choosing the "Merge Cells" command. A close-up of a spreadsheet on a computer screen.
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.
TEXTJOIN Function : The Microsoft Excel TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter. The TEXTJOIN function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.
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.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
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.
Undo Command: The Undo feature in Excel 2010 can quickly correct mistakes that you make in a worksheet. The Redo button lets you “undo the Undo.” The Undo button appears next to the Save button on the Quick Access toolbar, and it changes in response to whatever action you just took; the Redo button becomes active whenever you use Undo.
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.
