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

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 webcast.

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
  • Explore how to apply Excel’s IFERROR function to compensate for temporary situations where a formula may return an error.
  • Recognize the benefits of using range names.
  • Discover how to display all formulas in a worksheet at once with the Show Formulas feature.
Last updated/reviewed: August 18, 2023
10 Reviews (38 ratings)

Reviews

5
Member's Profile
Depending on the student's knowledge of Excel this may be a little basic. However, there are several little tricks to incorporate. This instructor performs a double presentation of the material, it is great if you are unfamiliar with what is being taught, it can be redundant if you are more knowledgeable about the subject; but as a general approach I feel it is helpful.

4
Member's Profile
This course does not cover all complex formulas but gives useful tips for breaking down formulas, presenting formulas in an organized way, analyzing formulas, and simplifying presentation of complex formulas.

4
Anonymous Author
David documents his points very thoroughly. His notes can be used for reference later to reinforce his teaching. I highly recommend him as an instructor.

5
Anonymous Author
I thought this course was very interesting and helpful. I thought the instructor explained the topic very well. I would recommend this course to others.

4
Member's Profile
This was an excellent course. Many different useful tools and functions to improve using and deconstructing formulas in worksheets

5
Anonymous Author
I found the course quite useful. Lots of helpful tips for those that do not use a lot of the functionality of excel already.

4
Anonymous Author
I like this instructor a lot. He is very detailed and his illustrations are very helpful. His materials are very organized.

4
Member's Profile
sheet tabs on the worksheet called breaking down complicated formulas did not follow the presentation slides

5
Anonymous Author
This course provided helpful tips on streamlining formulas and resolving errors in Excel sheets.

5
Member's Profile
Once again I learned a lot of tips I didn't know, despite being a longtime Excel user.

Prerequisites
Course Complexity: Intermediate

Program Prerequisites: Experience with Excel

Advance Preparation: None

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Efficiency: Breaking Down Complicated Formulas9:21
  Viewing Two Workshets at Once, Color -Coded Formulas and Safely Making Copies of Formulas8:25
  Evaluate Formulas6:08
  Use F9 to Calculate Part of a Formula and N Function9:17
  Show Formulas Feature and FORMULATEXT Function6:41
  Initiating the Formula Comment Tool5:10
  Unhide Personal Macro Workbook, Edit the Formula Commnet Macro, Copy/Paste Formula Code, Test the Formula ...8:10
  Create Range Names from Selection and Use in Formula Feature7:37
  Apply Range Names to Formulas and Table Feature11:20
  Table Formulas: Column Names vs. Cell References, Formula Management Within a Table, Remove Table Feature ...12:32
  Trace Dependents, Trace Error Command, Formula Auditing Keyboard Shortcuts and Types of Formula Errors7:05
  Introduction to IFERROR, ISNUMBER/ISTEXT, SUMPRODUCT/ISERROR and Conclusion8:19
CONTINUOUS PLAY
  Excel Efficiency: Breaking Down Complicated Formulas Full Video1:40:06
SUPPORTING MATERIALS
  Slides: Excel Efficiency: Breaking Down Complicated FormulasPDF
  Excel Efficiency: Breaking Down Complicated Formulas Glossary/IndexPDF
  Workbook: Excel Efficiency: Breaking Down Complicated FormulasPDF
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam