One of the most common ways to find spreadsheet errors is to look at the structure of the sheets and how the formulas have been copied (or not) and whether there are any overwritten formula or hardcoded numbers appearing in the middle of formula sections. Finding inconsistent and overwritten formula generally means you have found a spreadsheet error.
We highly recommend using an add in for this though. You can see how to use the inbuilt Excel tools for finding formula inconsistencies but practically an add in like ExcelAnalyzer is required.
Identify where the formula changes in a spreadsheet
Using ExcelAnalyzer, all the cells are colour coded. If the cells are the same colour, they are the same formula.
As shown below, the ideal spreadsheet should have a single formula copied all the way across as in (1). Note that at (2) the colour changes.
These are the risk areas of any spreadsheet. You will need to investigate why the change happened. They are also the easy wins. The spreadsheet developer will either explain the difference or not be aware of it, and it could be an error. Note that with ExcelAnalyzer, for bigger spreadsheets you can narrow the columns and zoom out to be able to see more.
Below another example but of a bigger spreadsheet. In order for you, manually, to find these 2 inconsistencies you would need to check every single cell and make sure you are concentrating to notice a formula change. Some changes may be as minor as changing a + to a – which is very hard for a human to spot.
Spreadsheet users also have a habit of overwriting formula with hardcoded numbers and then forgetting that the formula is no longer in the cell and no longer working.
In the case below, the spreadsheet is working in columns and you can see that all the colours flow down. But at one point they are all grey. Grey is an important colour to ExcelAnalyzer as it means a number. As seen below the formula suddenly stops and is overwritten with zeros which means that there could be a problem with the spreadsheet.
When you find an issue with the formulas not being consistent you will need to investigate it. We tend to use the inbuilt Excel Auditing Toolbar (learn more in our free Excel fundamentals course), or you can use ExcelAnalyzer’s Tracing Formula tools as shown below. It uses the Auditing Toolbar but it allows you to highlight a whole row or column and it will draw the lines for all the cells.
As shown below, where the colours have changed (meaning a different formula) it looks like it is looking one cell to the right instead of looking straight above. This looks like an obvious error and is a quick win, if you can find it quick enough.
First Step in any Spreadsheet Review
This is such a powerful test that you should do this first. Run the add in and then look at all the sheets as shown below. If you need to, narrow the columns and zoom in or out. You will quickly see the ‘funnies’. Looking at the image below which cells would you want to investigate more? To investigate you would use the Tracing Formula and Tracing input buttons and actually look at what the formula says.
