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).
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.
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.
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 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.