In this illuminating session, Excel expert David Ringstrom, CPA, shares techniques you can use to verify the integrity of even the most complicated Excel workbooks. David demonstrates Excel’s formula auditing and error-checking tools, as well as how to verify which cells contain formulas versus values. In addition, David explains how to: find buried worksheets; access password-protected workbooks; use the Conditional Formatting feature to identify duplicates in a list; monitor even minor changes to your workbooks; hide, display, and locate cells that contain comments; and much 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.
Covered Topics
- Adding a macro to Excel that adds the ability to display any formula in a cell comment.
- Auditing all named cells in a workbook with ease by way of the INDIRECT function in all versions of Excel and the FORMULATEXT function in Excel 2013 and later.
- Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value.
- Auditing the data source behind pivot tables in Excel spreadsheets.
- Bringing Excel’s green error-checking prompts under control by managing the underlying rules.
- Creating bookmarks and nicknames for key inputs by way of the Create Names from Selection feature
- Determining at a glance whether a spreadsheet contains links to other workbooks.
- Determining whether it’s safe to edit or delete a cell by way of the Trace Dependents feature.
- Differentiating between manual and automatic calculation modes in Excel, and when Excel may default into manual calculation mode.
- Displaying all formulas in a worksheet at once with the Show Formulas feature.
- Exploring options for recovering lost passwords for Excel spreadsheets.
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.