Finding hard coded values within Excel Formulas is something Excel users have struggled with for a long time. While I was researching this article, I am surprised that the solution I am about to offer you has not been widely covered so far.
What exactly is a hard coded value?
It would be common to see a formula in Excel such as =A1+A2. Let’s just say that cell A1 has a value of 10 and A2 has a value of 20. The result would be 30. If you entered 30 instead of the formula, this would be a hard coded value.
Now let’s say you have the formula =(A1+A2)/2. The result would be 15 however this formula has a hard coded value within it. This value is 2.
What problems does it cause?
Simple answer……a massive headache……..
- Hard coded values may not be updated by the user when the spreadsheet changes
- It becomes very difficult to remember where all your hard coded values are making your workbook impossible to manage.
- Hard coded values are sometimes used a plugs to get figures to balance, therefore the report is probably incorrect somewhere, and next month you might not be able to find the plug
- Hard coded values are sometimes entered as a test and then forgotten about, for example you might want to see what happens if the value is halved, but then take a call and forget about the change you made
Avoid Hard coding
Hard coded values within formulas are evil. Avoid with all costs using hard coded values within formulas. Best practice is to have an input sheet and a calculation sheet. Formulas should reference to the input sheet. This avoids the need for hard coding within formulas.
But let’s get back now to how you can easily find hard coded values within Excel formulas.
Find Hard coded values in Excel Formulas
The solution is rather simple. We will use Spreadsheet Inquire which is an add in available for Office 2013 & 2016 Professional Plus pro.
If you don’t see the Inquire tab, you just need to turn on the add in. To do this, go to File, then Options, then Add in’s. You then need to select COM add in’s in the Manage drop down box. This will bring you to a dialogue box with all the COM add in’s available. Select Inquire and then okay. The inquire tab should now be available.
Once you have the Inquire tab available, select Workbook Analysis. This will quickly analyse your workbook and return details based on the items selected in the panel on the left.
Under items, scroll down to Formula and then select ‘with numeric constants’.
In the results pane you will now see details of all formula within the workbook that constant a numeric constant or a hard coded value within a formula. If necessary you can export these details into Excel and carry out any investigation that is necessary.