Course Description
In this course, Excel expert David Ringstrom, CPA, shares techniques you can use to deconstruct and verify any type of formula in Excel, including dynamic array formulas in Excel 2021 and Excel for Microsoft 365. Techniques include safely making copies of formulas, identifying precedent cells, and utilizing the often overlooked Evaluate Formulas feature.
David 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:
Professionals who review and audit Excel spreadsheets created by others, or those who wish to improve the integrity of their own spreadsheets.
Level: Intermediate
Topics covered:
- Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value
- Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets
- Displaying subsets of data dynamically by way of the new FILTER worksheet function
- Utilizing the FORMULATEXT function to display a formula from one cell in another cell
- Identifying the various # sign errors Excel formulas can return
- Identifying other cells a formula relies on by way of the Trace Precedents feature
- Learning about the MINIFS function available in Excel 2019 and later
- Stepping through formulas in slow motion with the Evaluate Formulas feature
- Understanding how the Table feature automates formula management within lists
- Sorting lists of data dynamically from elsewhere in a spreadsheet with the new SORT function
- Understanding the nuance of editing dynamic array-based formulas in Excel 2021 and Microsoft 365
- Displaying all formulas in a worksheet at once with the Show Formulas feature
Learning objectives:
- Recognize a dynamic array function from a list of worksheet functions
- State what the FORMULATEXT displays when you reference a cell that does not contain a formula
- Identify the command that enables you to automatically assign range names to a block of cells
Review our course policies and procedures page for further information