How to Streamline the Excel Error Checking Process: Excel’s Inspect Workbook Feature

by Matthew Kuo on September 28, 2015

in Error Checking, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

Checking for errors in your Excel files is always a pain.  It’s generally a very manual process that takes a lot of time, with a certain level of ambiguity regarding your return on investment – you could spend hours checking for issues in your Excel file, find nothing wrong, and your only tangible benefit is additional peace of mind.  Additionally, when you’re building a huge model housing databases or working against a tough deadline, it’s tempting to just skip the error checking process altogether and not allocate time towards quality control.

One way to address the efficiency aspect of error checking is a greatly underutilized feature in Excel called the Inspect Workbook feature (also known as the Document Inspector).

Insect_Workbook_01

Key Benefit

Inspect Workbook allows you to quickly check for a long list of issues that could potentially cause problems in your workbook.  The feature is built into Excel versions 2007 and later and covers a wide range of items.  Below is the full list of items that Inspect Workbook will check for:

  • Comments and Annotations
  • Document Properties and Personal Information
  • Data Model
  • Content Apps
  • Task Pane Apps
  • Pivotables, PivotChart, Cube Formulas, Slicers, and Timelines
  • Embedded Documents
  • Macros, Forms, and ActiveX Controls
  • Links to Other Files
  • Real Time Data Functions
  • Excel Surveys
  • Defined Scenarios
  • Active Filters
  • Customer Worksheet Properties
  • Hidden Names
  • Custom XML Data
  • Headers and Footers
  • Hidden Rows and Columns
  • Hidden Worksheets
  • Invisible Content

In terms of efficiency, the Inspect Workbook feature is extremely helpful because it can review each of these items in a single pass.  You can imagine how long it would take to check your workbook for all of these items manually.

From a usage perspective, the best time to leverage this feature is when you inherit a document from someone else.  Obviously if you’re just working in file by yourself, you’d probably know if you added macros or hid tabs in your workbook.  Therefore, Inspect Workbook is probably not be that useful if you’re just working alone.

The Inspection Process

To initiate the Document Inspector, all you have to do is go to Excel’s File menu.

Insect_Workbook_02

In that view, you should see the option to inspect your workbook in the middle of the screen.

Insect_Workbook_03

From there, just click on the button for Check for Issues and then select Inspect Document.

Insect_Workbook_04

Excel will remind you that it’s a good idea to save your file and give you the option to do so.  Click Yes to save and proceed, or No if you don’t want to save your file at that moment.

Insect_Workbook_05

Excel will bring up a menu asking you to confirm all the items you want to check for.  You can generally just leave these all checked, as adding items doesn’t really make the inspection process take any longer.

Click the Inspect button once you’ve confirmed your inspection list.

Insect_Workbook_06

Finally, Excel will return with a summary of all the items it found, and the option to remove some of these from your workbook.

Insect_Workbook_07

Key Limitations

While Inspect Workbook is great for the identification of errors, it’s actually not that helpful in terms of actual error resolution.  The best example of this is how it deals with hidden worksheets, shown below.

After running the Inspect Workbook tool, notice that Excel has identified that the worksheet I’m using has two hidden worksheets.  From this interface, my only option is to delete these hidden worksheets without even looking at them.  From an audit perspective, this would be a terrible idea.  Those hidden tabs could be holding values and calculations that are critical to the rest of the file.

Insect_Workbook_08

Ideally, what I’d want to do in this situation is unhide all tabs, inspect the ones that were hidden, and then decide if I actually want to delete them.  Excel’s Document Inspector lacks that interface, so for now, you still have to go through that process manually.

Additionally, Inspect Workbook does not identify formula errors in your file.  If you have reference errors or divide by zero errors showing up in your formulas, the tool does not have an option to call them out.  Excel’s error checking feature will call these out in individual cells, but it’s not really linked to the Inspect Workbook feature.

Summary

The best time to use Inspect Workbook is when you’re given a huge file that you know nothing about, are expected to inherit and own the file going forward, and need to look for mousetraps that could cause mistakes and errors down the road.  Depending on how and where you use Excel, you might use this feature often to manage handoffs.  Other people who just work in Excel by themselves may not use it at all.  At the end of the day, it’s important to be aware that this feature exists, so if you ever do need to audit a massive Excel file, you can save yourself a significant amount of time.  Just note that, for the actual error resolution, you’ll still have to do most of that manually.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post:


Related pages


or in countifsdraw bar graph in excelucla bookstoreexcel vertical lookupbackward vlookuphow to put a histogram in exceleasy formulas in excelstudent technology center uclaexcel formula refhow to create a histogram in excelformulae in excelusing vlookup excelvlookup using 2 criteriaexcel replace duplicates with blanksvlookup with multiple conditionsrandom name picker free downloadprimo pdf softwaretasks template excelcompare two excel sheets for duplicatesexcel vba step by step tutorialfind duplicate email addresses in excelshort cuts for excelexcel match deutschrandom partner pickercountif in rangeexact bins lookupfinding duplicate in excelvlookup table arrayinsert check mark in excel 2007excel plot chartjohn isnatax brackets 2015 calculatornormal distribution calculator exceluid uclaexcel vba lookup value in arrayhow do i get excel to automatically calculatehow to transpose data in excel 2007bell curve generatorexcel function commandsexcel vlookup functionsadding columns in excelhistogram excel templateconditional statements excelvba isnawingdings 2 font downloadformatting charts in excelselect duplicate rowsexcel random selection from listcurved line graph makerhow to learn formulas in excelcount formula in excel with exampleinsert total row excelexcel formula cheat sheet pdfhow to remove shading in excelpowerpoint tick symbolcheck mark symbol microsoft wordexcel blank cell valuehow to delete duplicates on excelshortcut for checkmarkmba behavioral interview questionsvlookup 2 valuesirs witholding calcmicrosoft excel formulas tutorialwhat is a histogram charthistograms binshow we use vlookup in excelthe goal a process of ongoing improvement cliff noteshow to write a vlookup formula in excelexcel 2007 remove duplicatesexcel graph formattingremove duplicates in excelexcel insert check markhow to make a vlookupifcount function in excelvlookup easy exampleluvalle bookstore uclahistogram creator onlinethe goal by goldratthlookup definition
\n