From time to time, I am asked to audit a spreadsheet.
One of the common mistakes I see is an inconsistent formula inside a group of cells.
– A1 has the formula =G1 * 0.105
– A1 has been formula auto-filled to A1:E5
– Then some time later, cell A3 has been changed to =G3 * 0.107
So the assumption is that A1 can be safely auto-filled to the region of cells. However, we have an exception which should be noted.
It’s nice to be able to highlight these exceptions. Here are 2 ways.
1. If you have Excel XP and above, you can use the Error Checking options. A little green triangle appears telling you that “something strange” is going on.
One downfall of the Error Checker is that it wont trigger if the inconsistent formula is on the corner of the checked region. In our example above, if the changed formulas was A5 instead of A3, it would have ignored that inconsistency.
I assume that this is to accommodate subtotals and grand totals?
2. Write a bit of VB code using the idea that the R1C1 version of the formula should be identical for all cells in the selection, so it should be a simple loop to check all of the formulas.
Here is an example:
Dim strFormula As String, rng As Range
strFormula = Selection(1).FormulaR1C1
For Each rng In Selection
If rng.FormulaR1C1 <> strFormula Then rng.Interior.ColorIndex = 6
To use it, select the range A1:E5 then run the macro. It would colour the inconsistent formulas yellow (in the first example, cell A3).