Detect hardcoded numbers in formulas

Your model doesn’t contain hardcoded numbers…or does it?

Avoiding hardcoded numbers in formulas while building is often not enough. What if your model already contains hardcoded numbers because you are modifying an old model? Therefore, being aware of any fixed numbers in your model is crucial, but how can you ever be sure you’ve found them all? An Excel model contains many formulas, and errors are always around the corner. In short, manually performing this task isn’t ideal, but let’s attempt it, nonetheless.

Step 1: Show formulas

We’ll start by making all formulas visible in our sample file. To do this, select the option “Show Formulas” under “Formulas” in Excel’s main navigation bar.

Step 2: Check every worksheet

On the first tab (“Summary”), we see various references to other tabs and some subtractions in column D and on line 6, but there are no hardcoded numbers visible.

On the second tab (“Income”), we mainly see directly entered values, some subtractions in column D, and some additions on line 8. But again, no hardcoded numbers.

On the third tab, we see some references to other tabs again, some subtractions, and some additions. So far, still no hardcoded numbers are found.

On the fourth tab (“Personnel”), we see the same picture as on the second tab: some directly entered values, some subtractions, and some additions. Are you feeling bored yet?

Well, at last, the situation on the fifth (and last) tab (“Operating”) is different. Here we see a formula in column C containing the hardcoded number 0.18, and in cell E23, we see an addition of +30.

Step 3: Isolate the hardcoded number and define a name

Let’s first deal with the number 0.18 in column C. The name of the column suggests that 0.18 is a VAT percentage. If this percentage ever changes, you may overlook it quickly because the formula is normally hidden. If you think about it, you’d have to adjust it for each cell in the column.

To solve this, we create a new tab: “Variables” where we note the VAT percentage with a clear label.

Then we define a name for the percentage, via Formulas -> Define Name. We call the percentage “VAT_PERC.”

Step 4: Apply the defined name

Go back to the fifth tab (“Operating”), where the hardcoded VAT percentage was found. Replace the hardcoded number (0.18) in the formula with the name “VAT_PERC.”

This method has a significant advantage. If the VAT percentage changes, for example, from 18% to 21%, we only need to adjust it in one place (on the “Variables” tab). In all places where the name “VAT_PERC” is used, it will be calculated with 21% from that moment on, greatly reducing the chance of careless mistakes. Nothing changes in the outcome of the formula, but it’s become much more transparent and much easier to update.

Step 5: Move hardcoded number to a more suitable column

Next, there is the number +30 in cell E23. ’30’ is a hardcoded number whose origin is unclear. Interpreting the table, we see that 30 is added to the difference between Estimated and Actual, resulting in an additional cost of 30. These additional costs are currently ‘hidden’ as an unexpected addition within a formula range. It is much more transparent to document these costs in column D (“Actual”) on a new ‘Other’ line and to extend the formula from column E, ensuring consistency throughout the entire table.

Step 6: Check the adjustment

With this adjustment, we see that the total difference (in cell E25) remains unchanged, but the total in column D (“Actual”) has increased by 30 compared to the initial situation.

This affects the total in cell C6 on the third tab (“Expenses”):

And thus, the results on our dashboard on the first tab (“Summary”) also change:

In summary

We have mitigated the risk of the hardcoded VAT percentage by replacing the hardcoded number with a name, and we have discovered and corrected a direct error in the model’s result (because the difference between Actual and Estimated costs has increased from 480 to 510).

Conclusion

Our sample spreadsheet is now much more reliable, but the process has taken us a lot of time. You can imagine that this approach is impractical and extremely time-consuming in the case of a (much) larger model.