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 as a plug 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. With all costs, you should avoid using hardcoded values within formulas. The 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 in Microsoft Office 2013 and later but you must have the Professional Plus Pro edition.
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 analyze 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.
Sign Up Now
Over to YOU
Do you use or have you encountered hardcoded values in Excel formulas? If so, have these ever caused you problems such as forgetting to change the value? Please do share your experience in the comments below.
I would suggest that you sign up directly for your own hive wallet and sign into steempress using your wallet. this way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet
>> GET HIVE WALLET NOW<<
If you sign up using the comments section below you will get a Steempress account. Steempress will hold any rewards you earn until you have a hive wallet.Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can contact us with this link.
Like what you see? I do hope that you will share this article across your social profiles
Cross posted from my blog with SteemPress : https://theexcelclub.com/find-hard-coded-values-in-excel-formulas/