Auditing Excel Spreadsheets (Completed)

In this presentation author and Excel expert David Ringstrom, CPA, shares techniques you can use to verify the integrity of even the most complicated Excel workbooks. Techniques covered include the Navigation task pane in Excel for Microsoft 365, as well as features available in all versions of Excel, such as Watch Window, Find Formulas, Trace Precedents, and the Evaluate Formula feature. You'll also see how to track down circular references and use the FORMULATEXT function to display formulas in any part of a worksheet.

David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who Should Attend
Practitioners who review spreadsheets created by others, or who wish to improve the integrity of their own spreadsheets.

Topics Covered

• Creating bookmarks and nicknames for key inputs by way of the Create Names from Selection feature
• Using the Workbook Statistics feature to determine the number of worksheets, formulas, cells with data, and more in a given workbook
• Verifying sums and totals quickly by simply selecting cells with your mouse
• Stepping through formulas in slow motion with the Evaluate Formulas feature
• Identifying duplicates in a list using Conditional Formatting
• Utilizing the Error Checking command to locate cells that contain errors within a worksheet
• Displaying all formulas in a worksheet at once with the Show Formulas feature
• Displaying all formulas in a worksheet at once with the Show Formulas feature
• Determining at a glance whether a spreadsheet contains links to other workbooks
• Hiding and displaying cell comments en masse as well as locating all cells that contain comments
• Auditing the data source behind PivotTables in Excel spreadsheets
• Determining whether it's safe to edit or delete a cell by way of the Trace Dependents feature
• Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value

Learning Objectives

• Identify the section on Excel's Home menu where the Microsoft Insider command appears, if available
• Recall the menu in Excel where the Table feature resides
• Recall the type of object that Python uses to store data within Excel workbooks

Level
Basic

Instructional Method
Group: Internet-based

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
A basic of understanding of Excel.

Advance Preparation
None