Finding all formulas with hardcoded numbers is one of the most important steps in any spreadsheet review. It is arguably the most common spreadsheet error. All the formulas in an area say =I4*(1+$B$4) but one of them says =J4*(1+5%). Is the 5% correct or did someone make a mistake? Using the tools in Excel these are hard to find without going through every single cell. A tool like ExcelAnalyzer highlights these immediately.
Listing all the cells with hardcoded numbers
As shown below, the Formula Report shows all the hardcoded numbers found in formula i.e. the cell starts with = and then after that, whether individually or within a function, there is a typed number. Using the tool you can list all the formulas with a particular number and then, if it looks like a possible spreadsheet error, you can click on it and see the formula in context within the spreadsheet. Below we show some of these.
Which hardcodings are riskier than others- investigate these first
Excel functions are built in such a way that you will sometimes be typing numbers into the function so some hardcoding is expected. Also, spreadsheet users often work in thousands or millions so there are multiplication/ division by these types of numbers. However, when you have a list of the hardcoded numbers you can prioritise which ones to look at first for your quick wins, hardcodings that are most likely to be a real spreadsheet error.
So below the ones we would look at first. It does not mean that the others are OK, it just means that the odds of finding a spreadsheet hardcoding error are higher with a number like 19 or 21.4334… than a 1, 10 or 100. As another example, in South Africa the VAT rate changed from 14% to 15%. As a result the hardcoded number of 14% must be investigated as it is highly likely the wrong tax rate is being used.
Finding and Analyzing the cells with hardcoded numbers
Using the Formula Report tool you can choose which number worry you the most and investigate them, or else look at all the formulas with hardcoding in one place.
Below we pulled up the 5% number and you can see if appears on 2 sheets. Both look like spreadsheet errors and should be investigated.
Next we can look at the 7.2 which is a strange hardcoded number (and probably should rather be a link to an input cell). As shown below, it looks strange. We can investigate by clicking on the hyperlink but there is a good chance there is a spreadsheet error here.
If you want to see all the hardcoded numbers within the formulas you can use the ‘Show All’ option as per below. The tool will automatically colour all hardcoded numbers red so they are easier to see. Based on the below there are some spreadsheet errors (+5000, 5%, 1985) and some are less risky and will be addressed later in the spreadsheet review process (3 inside the VLOOKUP, <1 in the IF function).
Find the obvious hardcoded errors fast
The key in this stage of a spreadsheet review is to find the obvious errors fast. By prioritizing the numbers that are higher risk you will quickly identify any spreadsheet errors that will have a meaningful impact on your business decision.