One of the most common reasons for errors in spreadsheets is inconsistent formulas in Excel. You look at the first few cells and the formula looks exactly the same so you assume that the whole row is correct, but hidden somewhere is an inconsistent formula that will cause chaos later. The idea of consistency is one of the good spreadsheet practices to be encouraged/ enforced. So how to find inconsistent formulas in Excel?
As per the image below, for some reason cell I13 is suddenly looking at 7% instead of 5%! This is easy to spot in a small spreadsheet, but can you make it easy with a big spreadsheet with multiple sheets and thousands of cells?
Below are some easy ways to find if there are any inconsistencies in a row or column of formula. We have ordered them in our opinion of usefulness
Maps are not currently inbuilt into Excel, but as it is our first port of call for any spreadsheet, we have included it here. It is part of the Excel Auditing Tool package.
In the spreadsheet below, just by looking at the image, can you tell me where the errors are?
Now if we run a map (see below) we get a visual representation of the spreadsheet, where it is clearly shown where the labels (L), numbers (N) and formulas (F) are. More importantly it also shows you where a formula has been copied across (<), down (^) or both (+). Now it is easy to see where the inconsistent formula are and to investigate them first. To see more about Maps go to the Auditing a Spreadsheet Course or the Maps page
If you spend any significant time reviewing spreadsheets, we highly recommend you invest in this tool.
GOTO Special- inconsistent rows/ columns
NOTE: For the rest of the page you can download the ‘How to find inconsistent formulas in excel‘ worksheet to follow along
An underutilised tool is the GOTO Special tool and in particular the Show Row/ Column Differences. This tool will look at a row or column of cells and show you which cells differ in formula from the first cell highlighted.
Below we have highlighted Row 20 (same row as 27 in the map example above).
Follow along in the sheet 1-Example of the spreadsheet download
If you click on HOME, FIND&SELECT, GOTO Special, you get the following options. In this case, as we have highlighted a row, we choose row differences.
When you click OK you will notice that cell I20 and J20 remain highlighted. This means that these 2 cells differ from the first cell highlighted (F20).
In order to properly investigate them, we would colour them another colour and then compare them to the first cell, probably using the Auditing Toolbar. This method works best for long rows or columns. Shorter rows/ columns can be checked with the Auditing Toolbar shown below.
The problem is that you have to go row by row whereas the maps effectively do this for you all in one go.
We love the Formula Auditing Toolbar as it visually shows us what the formula is using. You can find the Formula Auditing tools in the formula tab.
There is much more information and a video clip in the Auditing Toolbar Tutorial, but here we will focus on trace precedent. The Trace Precedent button shows you which cells are used in a calculation i.e. which cells PRECEDE the cell you are in.
If you click on a cell you are investigating and push the Trace Precedent button you will see some blue lines appear as shown below. Note that the blue lines point to the cells referred to in the formula bar i.e =F11*(1+$E$4).
Visually it is much easier to see what is being used this way.
We often use this feature to trawl through a row or column of formula to identify cells that have inconsistent formula in them. The idea is that a well built spreadsheet has consistency in formula and the pattern shown by the blue lines should be similar. In the example below you can quickly see that there is something different with cell I11 as all the other cells refer to the 5% and this one refers to the 7%.
Unfortunately you have to go cell by cell as you cannot highlight the whole row and get everything at once.
Show all formula
Another option is to see all the formula in a spreadsheet instead of the results. Below, by clicking on the FORMULA tab, and SHOW FORMULA, you will see that the spreadsheet we have been using above, now appears with all the formula shown instead of the results of the formula.
This is great if you have major differences (e.g. a formula is overwritten with a number). If you click on a cell, the coloured box also automatically appears (e.g. I13) so you can see where it is looking.
To switch it off you just click the SHOW FORMULA button again (or CTRL ~ as a shortcut).
This works well but it still relies on you to spot the difference and sometimes it can be just an extra ) or a – sign. The maps will automatically tell you there is a change.
Change to R1C1 format to spot differences (but change back)
The problem with the SHOW FORMULA option is that it is still difficult to spot a problem because each cell refers to a letter and number and these are changing all the time.
An option is to change the referencing to rather use R1C1 (R6C6 means row 6 column 6 and is the same as cell F6. R-9C-1 means 1 column back and 9 rows up from the cell). You can change your settings by clicking on FILE, OPTIONS, FORMULAS and ticking the R1C1 reference style.
When you use SHOW FORMULAS again it will look like this. To us it is still confusing and we would rather use maps but at least here you can see where the reference goes to compared to the cell i.e. always 6 rows up.
Just don’t forget to switch R1C1 off, otherwise the next person to use your spreadsheet will be very confused.
GOTO Special- highlight constants/ formula
If you are more worried about significant changes where a formula has been overwritten with a number, you can use the GOTO Special tool to colour all cells that contain numbers one colour and all cells that contain formula another colour.
On your spreadsheet, click on HOME, FIND& SELECT, GOTO SPECIAL, and check the CONSTANTS and choose NUMBERS (you only want to see all cells containing numbers).
Follow along in the sheet 2-Example
When you click OK, these will be highlighted. You can colour them a light blue for example. Do the same process again but check FORMULAS and tick NUMBERS again (you want all cells that contain formulas that end up as a number). Colour them a light orange. As shown below, you can easily see that cell J23 has been overwritten with a number.
The problem with this method is that if someone types =130059, it would be treated as a formula and you wouldn’t pick it up.
Error Checking Rules (green marks in the top left of cells)
Another option is the error checking rules that are available in Excel. You may have seen them as the green marks in the top left corner of cells (see below).
If you click on a cell with one of these e.g. I13 above, you will see an Exclamation Sign. If you hover over it, it will specify the type of error it has found.
In order to activate/ deactivate these you can go to FILE, EXCEL OPTIONS, FORMULAS and ENABLE BACKGROUND ERROR CHECKING.
Note that there are a number of tests it runs. We have highlighted the 2 tests related to inconsistency in a cell compared to cells around it.
We must admit that we normally put this feature off. The reason is that there are so many of them eventually you become blind to them. The tests themselves are good though.
You should always be able to find inconsistent formula in your spreadsheets. We prefer using the Maps from Spreadsheet Professional as it covers all of the other tools in a nice logical way where the computer does all the work.