In this session, Excel expert David Ringstrom, CPA, continues the auditing discussion he started in Part 1 of this series.  You see how retrofitting existing spreadsheets with range names can transform the auditing process. You learn to determine at a glance if a spreadsheet contains links to other documents, as well as explore nuances surrounding cell comments, plus much more.

In this presentation, David demonstrates techniques in Excel 2016, but where applicable, he notes any differences in Excel 2013, 2010, or 2007. His webcasts 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:

• Find and select all formulas within a worksheet with just a couple of mouse clicks.

• Use the N function to annotate formulas within the formula bar when a cell comment won’t suffice.

• Learn how range names can minimize errors, save time in Excel, serve as navigation aids, and store information in hidden locations.

• Go beyond simply adding cell comments: easily hide, display, and locate all cells that contain comments.

• Audit all named cells in a workbook with ease by way of the INDIRECT function in all versions of Excel and the FORMULATEXT function in Excel 2013 and later.

• Track down the ripple effect of errors in spreadsheets with Excel’s Trace Error command.

• Go behind the scenes in a workbook to check for worksheets that can’t be viewed or hidden from Excel’s primary user interface.

• Print a list of all cell comments on a worksheet or print visible comments.

• See how to view two worksheets from within the same workbook at the same time.

• Simplify formula auditing by way of the Apply Names feature.

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
  • Identify the keyboard shortcut that launches Excel's Visual Basic Editor.
  • Recognize the hidden formula tracing capability that turning off Allow Editing Directly in Cells enables.
  • Discover the location of the menu command that allows you to select  all cells that contain formulas in a worksheet.
Last updated/reviewed: March 22, 2024
5 Reviews (29 ratings)

Reviews

5
Member's Profile
as with spreadsheet auditing 1, this course explores - and does a great job of explaining - a lot of functionality that I seldom touch. there are solid takeaways that are immediately useful to me in my work.

5
Member's Profile
Again many powerful utilities and commands to help you fix complex workbooks

5
Member's Profile
I like that this course shows areas of Excel that I haven't explored much.

5
Member's Profile
This course is very help

5
Anonymous Author
Excellent!

Prerequisites
Course Complexity: Advanced

No Advanced Preparation or Prerequisites are needed for this course. However, it is recommended to take the other courses in the series prior to completing this one.

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 211:02
  Apply Range Names to Formulas; Auditing Named Cells; Error Checking Command10:52
  Confirming If a File Has Links; Cell Comments; Printing Cell Comments10:20
  Allowing Editing Directly in Cells and N Function9:16
  Enabling Track Changes; Spreadsheet Edits; Reviewing Edits; Unhide Very Hidden Worksheet9:50
CONTINUOUS PLAY
  Excel Efficiency: Spreadsheet Auditing Part 251:20
SUPPORTING MATERIALS
  Slides: Excel Efficiency: Spreadsheet Auditing Part 2PDF
  Excel Efficiency: Spreadsheet Auditing Part 2 Glossary/IndexPDF
  Excel Efficiency - Spreadsheet Auditing Part 2 ExamplesXLSX
REVIEW AND TEST
  Review Questions quiz
 Final Examexam