Breaking Down Excel Formulas

Author: David H. Ringstrom

Sometimes your formulas can grow out of control, or, more likely, you’ve inherited spreadsheets from others that you’re to take ownership of. In this comprehensive presentation, you’ll learn from Excel expert David Ringstrom, CPA, multiple ways to make sense of complicated formulas. After this on-demand course, you’ll have many tricks at your disposal to quickly decipher even the most complex Excel formulas.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 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 course.

Publication Date: November 2018

Topics Covered

• Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value.
• Determining whether it's safe to edit or delete a cell by way of the Trace Dependents feature.
• Displaying all formulas in a worksheet at once with the Show Formulas feature.
• Giving yourself more room to work by expanding the formula bar when needed.
• Identifying other cells a formula relies on by way of the Trace Precedents feature.
• Leveraging Excel's color coding to detect cells related to a formula, especially in Excel 2013 and later.
• Making copies of formulas safely to provide you with a fallback position.
• Shortening worksheet names, even temporarily, to make formulas easier to comprehend.
• Stepping through formulas in slow motion with the Evaluate Formulas feature.
• Utilizing keyboard shortcuts to identify precedent and dependent worksheet cells.
• Utilizing the FORMULATEXT function in Excel 2013 and later to display a formula from one cell in another cell.
• Utilizing the New Window and Arrange Windows commands to view two different worksheets simultaneously.

Learning Objectives

• Recall how to use the FORMULATEXT function to display a formula from one cell in another cell.
• List the benefits of using range names.
• Define how to use the F9 key to temporarily convert part of a formula to a value.

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience with Excel functions.

Advance Preparation
None