Auditing an Excel spreadsheet

Find errors in pivots, charts, conditional formats and data validation

This is a preview lesson

Purchase this course, or sign in if you’re already enrolled, to take this lesson.

There are certain spreadsheet errors which are just difficult and time consuming to find. These are typically in other Excel tools, so not directly in a cell, generally something that is kind of hovering over cells. Below details in finding errors in pivots, charts, conditional formats and data validation. Unless you are going to manually click on each object and investigate, the spreadsheet analyser add in is critical. Any errors found in these are likely to be actual spreadsheet errors.

Errors in Pivot Tables

If a spreadsheet has Pivot Tables, the most likely cause of errors is where the Pivot is missing some source data or else it is looking at the incorrect source data. Using the Detailed Report in Excel Analyzer, you will get the listing (as shown below) with some other useful information (where the source data is, has the source data been deleted, has it been refreshed recently).

errors in pivots, charts, conditional formats and data validation

In order to check if a Pivot includes all the source data, you will need to click on it and investigate the source data. This is as easy as clicking on the hyperlink and then in the Pivot Tables, and clicking on Change Data Source. As shown below it seems to miss rows 19 to 31 in the data source. What looks like an obvious spreadsheet error found quickly.

errors in pivots, charts, conditional formats and data validation

Finding spreadsheet errors in Charts

With Charts the most likely error is that some data has been deleted or else it is looking at a different sheets data (happens a lot when you copy/ paste charts). Difficult to find errors if you are doing the checks manually. As shown below, the Spreadsheet Analysis lists all the charts and:

  • (1) identifies if there are any charts with errors
  • (2) specifies which one has the error (if you go look at this chart, the one series has a REF error in it.
  • (3) lists charts that use data from another sheet. This is often accidentally from a copy paste.
  • (4) if you hover over the chart name you see an image of it which helps you remember what you are looking at.
errors in pivots, charts, conditional formats and data validation

Finding errors in Conditional Formats

Conditional formats can have errors in their formulas such as a REF or NA. Finding conditional formats is extremely difficult manually as, unless they are on and completely different from normal colours you may need to check every cell.

It is easier to use the Detailed Report from ExcelAnalyzer. Not only does it list all the Conditional Formats and you can click the hyperlink to see them, it also brings through the conditions used AND highlights the ones with errors. Quick identification of an otherwise difficult to find spreadsheet error.

Errors in Data Validation

The most likely error is a range that has been deleted. These are the easy errors. Depending on the complexity of the spreadsheet you may need to spend a bit of time to understand what the Data Validation is controlling, but for the quick wins we are looking for the obvious errors.

The Data Validation report shows a listing of all the Data Validations in a spreadsheet ( you will be surprised how many are just left behind) and any obvious (to the tool anyway) errors (you try find these manually). As shown below there is a REF error in a dropdown list, presumably because the list has been deleted.

errors in pivots, charts, conditional formats and data validation

Errors in Named Ranges

Often the cause of phantom External links, errors in named ranges seem to hide in full view. As shown below, besides the listing which is easier to work with, you can also see what it refers to. If you see references to external spreadsheets these could be the source of your phantom links (links you can’t seem to get rid of when using the EDIT LINKS tool and the Break Links button). You will also see below where the source has Ref Errors.

Spreadsheet Errors in Objects

These errors are typically quick wins. Within a short space of time you will know if there are any obvious errors to follow up on and, based on your knowledge of the spreadsheet, whether the impact of the error is significant or not.

YouTube finding errors in Pivot Tables, Charts, Conditional Formatting, Data Validation and more

0 of 26 lessons complete (0%)
    Your Cart
    Your cart is emptyReturn to Shop