Instructor for this course
more

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: November 4, 2019

3 Reviews (13 ratings)Reviews

5
Anonymous Author
This course provided helpful tips on streamlining formulas and resolving errors in Excel sheets.
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 found the course quite useful. Lots of helpful tips for those that do not use a lot of the functionality of excel already.

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 .
Course Syllabus
INTRODUCTION AND OVERVIEW
  9:21Introduction to Excel Efficiency: Breaking Down Complicated Formulas
  8:25Viewing Two Workshets at Once, Color -Coded Formulas and Safely Making Copies of Formulas
  6:08Evaluate Formulas
  9:17Use F9 to Calculate Part of a Formula and N Function
  6:41Show Formulas Feature and FORMULATEXT Function
  5:10Initiating the Formula Comment Tool
  8:10Unhide Personal Macro Workbook, Edit the Formula Commnet Macro, Copy/Paste Formula Code, Test the Formula ...
  7:37Create Range Names from Selection and Use in Formula Feature
  11:20Apply Range Names to Formulas and Table Feature
  12:32Table Formulas: Column Names vs. Cell References, Formula Management Within a Table, Remove Table Feature ...
  7:05Trace Dependents, Trace Error Command, Formula Auditing Keyboard Shortcuts and Types of Formula Errors
  8:19Introduction to IFERROR, ISNUMBER/ISTEXT, SUMPRODUCT/ISERROR and Conclusion
CONTINUOUS PLAY
  1:40:06Excel Efficiency: Breaking Down Complicated Formulas Full Video
SUPPORTING MATERIALS
  PDFSlides: Excel Efficiency: Breaking Down Complicated Formulas
  PDFExcel Efficiency: Breaking Down Complicated Formulas Glossary/Index
  PDFWorkbook: Excel Efficiency: Breaking Down Complicated Formulas
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM