In this session, Excel expert David Ringstrom, CPA, shares some of his favorite spreadsheet auditing techniques. You see how to enhance Excel with the ability to instantly add a worksheet comment that displays the underlying formula within a worksheet cell. You also learn about the under-utilized Watch Window feature, tracing cell precedents and dependents, and explore the nuances of circular references in Excel. In this presentation, David demonstrates techniques in Excel 2016, but where applicable, he notes any differences in Excel 2013, 2010, or 2007. His courses are fast-paced, and attendees often are surprised at the amount of ground he covers in a session. David's detailed handouts, with numbered steps, serve as reference material you can utilize going forward. He also provides an Excel workbook that includes the examples he uses to demonstrate techniques during the presentation. 

Topics covered:

  • Use the Watch Window to easily monitor the ramifications of even minor changes to your workbooks.
  • Understand the difference between manual and automatic calculation modes in Excel.
  • Move through formulas in slow motion with the Evaluate Formula feature.
  • Verify sums and totals quickly by simply selecting cells with your mouse.
  • Discover how to use Excel’s Personal Macro Workbook.
  • Learn two ways to instantly display all formulas within a worksheet.
  • Learn the nuances of circular references in Microsoft Excel
  • See the pros and cons of using the F9 key within Excel’s formula bar to evaluate a portion of a formula.
  • Learn how to trace dependents and precedents, both with Excel features and keyboard shortcuts.
  • Enhance Excel by creating the ability to display the formula within any worksheet cell in a cell comment.
  • Learn techniques for accessing password-protected workbooks when the password is not known.

Course Series

This course is included in the following series:

2 CoursesExcel Spreadsheet Auditing

  1. Excel Efficiency: Spreadsheet Auditing Part 1
  2. Excel Efficiency: Spreadsheet Auditing Part 2
Learning Objectives
  • Discover how to manage iterative calculations (also known as circular references) within Excel workbooks.
  • Identify the Show Formulas feature in Excel and recognize its functionality.
  • Discover the mouse action the mouse action that enables you to move from one end of a tracing arrow in Excel to the other.
Last updated/reviewed: August 5, 2023
31 Reviews (81 ratings)

Reviews

5
Member's Profile
about 75% of my work time is spent in spreadsheets, but while I consider myself proficient, there are vast swaths of the program that I never touch. This course - and this is true for all of the Ringstrom courses I've taken - did a great job of identifying a set of functions that I barely touch, explained how they can be useful, and his explanations are always thorough.

4
Anonymous Author
David is a very effective instructor. His way of teaching is very good as he explains it twice - first with steps and second with the actual execution. I am a heavy user of excel but learned a lot from this course - things that I did not even know exist. I started taking more of courses where David is the instructor.

5
Member's Profile
This course has some interesting information I was not aware of. I liked the idea of finding all the brackets ([) to locate all the links. I also learned how to drag a cell to another tab in the same workbook. I have used Excel for years, but I didn't know you could do that.

3
Anonymous Author
Was confusing at times. Not sure if there is a way you can listen to audio and have the workbook open following the steps. I listened on an iPad - did the steps on an excel workbook I created.

5
Anonymous Author
В целом курс считаю полезным. Предлагаю в качестве экзамена решать задания в терминальном доступе

4
Anonymous Author
A lot to learn; especially when trying to follow along in excel. Jumbled in a few places but, overall, a good presentation. Time could be extended to an hour and a half.

4
Anonymous Author
David did a great job explaining these auditing tools in Excel. I had always wondered what the "Watch Window" did and now I know.

5
Member's Profile
David does a great job at demonstrating an awful lot of useful features inside the Formula tab that can be very very helpful.

3
Anonymous Author
Interactive excel sheets would be easier to follow. The static image with call out boxes makes it difficult

4
Anonymous Author
Solid information. The Watch Window and confirming links sections were the most useful tools I took away.

5
Anonymous Author
Very informative and used features I wasn't familiar with and I think I will use in the future. thanks!

4
Member's Profile
Great Course with excellent examples. I knew of these tools but not really how to use them. Now I do.

4
Anonymous Author
Some useful tips on how to audit existing spreadsheets as well as how to keep formulas tidy.

4
Member's Profile
I learned a lot in this course. I had no idea that the watch window existed. Very cool tool!

5
Member's Profile
Good course narrated many introductory topics ,included workbook was great for practice

4
Member's Profile
Excellent, I learned a few tricks I was not aware off.

5
Anonymous Author
comparisons between Excel versions was very helpful.

5
Member's Profile
Great! Well done.. Thank you so much for sharing.

5
Member's Profile
Good tips for how to use Excel functions

5
Member's Profile
Good introduction with helpful examples

4
Anonymous Author
Good review of auditing functionality.

4
Anonymous Author
Great tips and tricks in this course!

4
Member's Profile
Good tips and review techniques

4
Anonymous Author
It was a very good course

5
Member's Profile
This is a great course

4
Member's Profile
Learned good tips.

5
Member's Profile
Very Informative.

5
Member's Profile
Good stuff here!

5
Anonymous Author
Informative.

5
Anonymous Author
Informative

5
Anonymous Author
Very good.

Prerequisites
Course Complexity: Intermediate

No Advanced Preparation or Prerequisites are needed for this course.

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: Spreadsheet Auditing Part 19:28
  Intiating the Formula Comment Tool and Window Watch13:24
  Evaluate Formulas and F9 Key 10:24
  Trace Dependents, Trace Precedents, and Circulure Refrences9:11
  More on Circulure Refrences and Recovering Lost Passwords9:20
Continuous Play
  Excel Efficiency: Spreadsheet Auditing Part 151:47
SUPPORTING MATERIALS
  Slides: Excel Efficiency: Spreadsheet Auditing Part 1PDF
  Excel Efficiency: Spreadsheet Auditing Part 1 Glossary/IndexPDF
  Excel Efficiency: Spreadsheet Auditing Part 1 ExamplesXLSX
REVIEW & TEST
  Review Questions quiz
 Final Examexam