Search

# How to check for hard-coded values in Excel formulas?

Share

Here is a common problem. Imagine you are looking a complex spreadsheet, aptly titled “Corporate Strategy 2020.xlsx” which as 17 tabs, umpteen formulas and unclean structure. Whoever designed it was in insane hurry. The workbook has formulas like this, =SUM(Budget!A2:A30, 3600)+7925 .

It was as if Homer Simpson created it while Peter Griffin oversaw the project.

So how do you go about detecting all cells containing formulas with hard-coded values?

### Alas, the usual methods fail

The usual methods to audit formulas are of no help here. Let’s see:

Show formulas (CTRL+`): Since we have way too many formulas, this approach requires a lot of squinting and gallons of coffee.

Go to special > Constants: This will only detect constant cells (ie input cells), but not cells containing formulas like =IF(2=2, Budget2014!A2, Budget2015!A2)

Trace Precedents: This can be used only for formulas that contain all hard-coded values (ex: SUM(1,2,3) will have no arrows, where as SUM(A1,A2, 7) will have some arrows

FORMULATEXT(): There is a new function called as FORMULATEXT() introduced in Excel 2013. This can tell us what is the formula in a cell. But we still need to develop additional logic to see if the formula text contains any constants.

### Let’s build ‘Detect hard-coded formulas’ feature for Excel

The beauty of Excel is that, if there is something you can’t do with on screen features, you can build it. This is where VBA comes handy.

So we can create a hasConstants() user defined function that takes a cell as input and tells us TRUE or FALSE. True if the cell has constants (or hard-coded values) as formula parameters and False otherwise.

But what should be the logic for hasConstants()?

The process for detecting hard-coded values can be defined like this:

1. Read the formula from left to right
2. For each argument of the formula
1. See if the argument is a valid reference or name
2. If not, break the loop and return TRUE
3. Return FALSE

How do we detect only the parameters?

There is no direct way to extract only the parameters of a formula. So what we do is we split the formula in to an array using the delimiter COMMA.

And we check each item of this array to see if it is

• a function call (like SUM, COUNT, VLOOKUP)
• a valid name or reference

The approach works the same way.

What about arithmetic, text or comparison operations?

For example, a formula like =A1+A2+17 should throw TRUE as it has hard-coded value.

So what we do is, we replace all such operators with delimiter (COMMA) before splitting the formula text.

We can consider +-*/%&><= as operators.

### So how does the code look like?

Here is how it looks like:

``````
Const COMMA = ","
Const OPERATORS = "+-*/%^&><="

Public Function hasConstants(thisCell As Range) As Boolean
'finds out if thisCell has a formula with constants in it
'i.e. hardcoded values

Dim formula As String, args As Variant, i As Long
Dim testRange As Range

formula = replaceOperators(Mid(thisCell.formula, 2))

args = Split(formula, COMMA)

For i = LBound(args) To UBound(args)
If Not (Len(args(i)) = 0 Or Right(args(i), 1) = "(" Or args(i) = ")") Then
'not a function or null, must be one of the parameters
'see if it is a valid name or reference
If Not nameExists(CStr(args(i))) Then
'name or reference doesn't exist, must be a constant / hard-coded value
hasConstants = True
Exit Function
End If
End If
Next i
End Function

Function replaceOperators(formula As String) As String
'replace operators such as +-/%^&>< with COMMA
Dim char As Long

For char = 1 To Len(OPERATORS)
formula = Replace(formula, Mid(OPERATORS, char, 1), COMMA)
Next char
formula = Replace(formula, "(", "(" & COMMA)
formula = Replace(formula, ")", COMMA & ")")
replaceOperators = formula

End Function

Function nameExists(name As String) As Boolean
'Check if a name or reference is valid
Dim testR As Range

On Error GoTo last

Set testR = Range(name)
nameExists = True
Set testR = Nothing
last:

End Function
``````

### How to use this code?

Simple. Copy this code and add it to your personal macros workbook. (Tip: how to setup personal macros workbook?)

Then use it in your complex workbook like this:

• To check if a cell contains hardcoded formulas, write =hasConstants(A1)
• To check if an entire range has hardcoded values,
1. Select the range
2. Go to home > conditional formatting > new rule
3. Select formula type rule
4. Type =hasConstants(top-left-cell relative reference)
5. Format by filling a color or changing font style to detect easily
6. Done

### Does it work in all cases?

For most normal formulas this approach should work. I have tested it with various combinations and it seems to hold up good. I suggest you to double check the results for any type II errors (ie missed hard coded formulas) during initial few rounds.

I never had the need to check for hard-coded values until recently. But once I had that need, I found there is no simple way to do it. I believe this kind of check can be very useful for people in modeling, risk management or auditing positions.

What about you? How do you check for hard coded formulas? What methods do you use? Please share your thoughts and tips in the comments section.

More on spreadsheet auditing & risk management:

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### How to fix SPILL Error in Excel Tables (3 easy solutions)

So you have a SPILL error in your Excel tables? In this quick article, let me show you 3 easy fixes to the problem. Fix

## Related Tips

Excel Howtos

VBA Macros

Excel Howtos

### Show difference between cells in status bar – VBA Example

Charts and Graphs

### 40 Responses to “How to check for hard-coded values in Excel formulas?”

Very good article and makes even better the conditional formatting trick. Thanks for sharing.

P.S: VBA may needs little alteration for array formulas. Invariably for subtotal function, function number is hard coded. And also for lookup functions, the exact match condition is hard coded.

• Jeff Weir says:

Good pickup. Same goes with VLOOKUP - either the Columns argument, or using 1 or 0 instead of True or False.

• Chandoo says:

Good catch. Implementing these nuances means adding more code to check individual functions. The macro would become very complicated when considering nested formulas. So I chose the happy medium.

Yeah. It may become lengthy cumbersome to cover all the cases. I just put my thoughts on this great forum.

On another note, is there any way to check the each substring EVAL with its text. If they both are same, they should be constant. Not sure how it works though 🙂

2. Jeff Weir says:

Interesting implementation. I like the way you substitute all operators with a comma.

Note you can replace this:
`For i = LBound(args) To UBound(args)`
...with this:
`For i = 0 To UBound(args)`
...which saves VBA having to evaluate the LBound, as it's always going to be zero (unless the user has set it to 1 in the declarations section).

I wonder if using the Evaluate method would be a quicker test of whether something is a range or not.

• Chandoo says:

Good suggestions Jeff. I have not thought about Evaluate approach. We could also invoke any of the simple application.worksheetfunctions like N(), T(), COUNTA() to check validity of names / references. (some of them only work if the name points to a cell, not to a value)

You can let us know if the EVALUATE approach is faster. To me the whole thing about setting a variable to range seems a little slow.

3. Elias says:

Chandoo,

None of the Worksheetfuntion you mention will work because they can use constant. However, what about counting the number of Row/Columns of the range.

Function nameExists(name As String) As Boolean
'Check if a name or reference is valid
Dim testR As Long
On Error GoTo last
testR = Range(name).Rows.Count
nameExists = True
last:
End Function

Regards

I apologize for my limited experience with VBA, but I copied the code into my personal workbook, as I've done successfully with other VBA code many times. Then I added the conditional formatting to a range that I knew had formulas with constants in it, but nothing happened. The one part that I may be misunderstanding is step 4 under "How to use this code?" It says "Type =hasConstants(top-left-cell relative reference)." Does that mean I should type "=hasConstants(C1)" if the upper left cell of the range I selected is C1? If so, it isn't working for some reason. I checked my Developer tab and my formulas were not set to use relative reference, but I changed it and it still doesn't work.

This was something I was just wondering yesterday how to do, so the solution is very timely and valuable to me.

Thank you!

Gary

Though I couldn't visualize your situation well, following may be of help.
1>Check the user defined function is working well. Just use “=hasConstants(C1)” and shall return true if C1 has some constants. If it is giving right answer, then VBA part is fine.
2>Conditional formatting. Select the cell in which you want to format. As shown in the figure above posted in the article, add conditional formatting with reference to same cell. When you select the cell to give reference in conditional formatting, by default it fixes the reference (like \$C\$1) which needs to be unfixed. Then use format painter to apply this conditional formatting to all cells.

5. Jeff Weir says:

I'd be interested to hear from any readers about whether they routinely have to identify cells with constants. We could include this in an add-in that my business partner and I are working on, but I don't want too many 'niche' functions cluttering up the interface. I don't know if this is a common requirement or not. If it is, please let me know.

• Jason says:

Jeff,

As an accountant, this would be useful. We often send budgets off to be reviewed and they are amended by people hardcoding values - a nuisance in any situation - which are difficult to find at times.

Jason

• NickC says:

Hi Jeff,

I'd use it too.

Cheers

Nick

Is there a way to make such a custom function available in the personal workbook? I love this idea, but I'd hate to have to copy and paste the code into each workbook I'm using that I want to use it in.

7. NickC says:

Great concept!

It seems like the conditional formatting trick only works when the UDF is in the workbook (not in Personal macro workbook), but still very handy to have.

Cheers

• Will says:

This is Great!

But I am having the same problem, is it possible to have the function available on all workbooks and also for it to work on conditional formatting?

Thanks!

8. Kuldeep says:

This is a good area if something can be developed. Many time this will be useful when you work on the workbooks you received from the external world.

Rather opposite to this is also a good idea.

if the file is using number of references within the sheet or across sheets, one may want to get all those cell (controlling element) at one sheet and control from there. we generally found this problem in workbooks where formula is build with many sheet reference to check. i generally have one dedicated sheet for all variables (reference) used in formula.

9. Oscar says:

I found this udf in David Hager´s Excel Experts E-letter (EEE) on J Walkenbach's website: http://spreadsheetpage.com/index.php/site/eee/issue_no_20_july_8_2001/

by John Walkenbach and John Green

---How can I locate cells containing formulas with literal values?---

Use the following UDF as your conditional formatting formula.

Function CellUsesLiteralValue(Cell As Range) As Boolean
If Not Cell.HasFormula Then
CellUsesLiteralValue = False
Else
CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"
End If
End Function

It accepts a single cell as an argument. It returns True if the cell's
formula contains an operator followed by a numerical digit. In other words,
it identifies cells that have a formula which contains a literal numeric
value.

You can test each cell in the range, and highlight it if the function
returns True.

• Kyle McGhee says:

Wow, so simple!
You could put that in conditional formatting or add a Sub that calls the function to handle ranges with a 'For Each Cell in Selection' loop

• Chandoo says:

Amazing find Oscar. It works for all numeric literals flawlessly and is far simpler than my macro. Thanks for sharing it with all of us.

• Jerome Caldwell says:

This udf is indeed wonderful, but don't we need to also return true if the cell contains simply a number, like:

23

and testing isNumeric(cell) will do that.

10. Roan says:

Chandoo - nice work. As usual, you've lit a fire and shown people how to do it. Now they'll be off using it to do things ranging from lighting a scented candle to stoking massive infernos that can melt metal.

Oscar - nice find. Sort, succinct and does the job (for the most part).

Jeff - your add-in sounds like a great idea. Like you say, catering for every exception on every function will get unwieldy, so maybe you can give the user a menu option to add their commonly-used functions to an exceptions list where they choose how each argument is dealt with (eg. check/ignore/NA). If you take VLOOKUP for example, whether you use TRUE/FALSE or 1/0 for the fourth argument, it should not affect the outcome of the UDF, since VLOOKUP can't work properly without it, so the fourth argument would be in the NA category (unless of course you get someone who does use a formula to determine the fourth argument!). For the third argument, many people want to use a constant as the column identifier, so would not want the UDF to highlight this. They would then choose Ignore for the third argument. I'd submit that most people would only ever add a handful of functions to the exceptions list, so it shouldn't slow down performance too much, but it will be of great benefit.

Thank you for your response. That was a good way to check the VBA code, and it seems there is a problem with the code. I copied it exactly as it is above into my Personal.xlsb workbook, under the Modules folder. In that folder I have a file called "MyFavoriteMacros," and I put all of my macros in it. When I did the test you mentioned on a cell that had "=149513.4+27.4" in it I got a "#NAME?" error.

Gary

NAME error comes for undefined functions. It seems like, the Macro is not loading.

12. David Brom says:

I've used a non-VBA solution in the past, though not sure if it covers every possibilities, but haven't had it come up wrong yet.

Assuming Cell A1 is the cell you're checking:
=IF(AND(CELL("prefix",A1)="",CELL("type",A1)"b"),TRUE,FALSE)

Oscar,

I copied the code into VBA and it highlighted one of the lines in red. Then I realized you said to use the code as your CF formula, so I created a new rule, and copied the code in as my formula, but nothing. This would be really helpful, but for some reason nothing seems to be working.

• Chandoo says:

You need to replace the " with

`"`

Chandoo I don't quite understand your reply. Is something missing, and who is this a reply to?

Thank you!

15. Neeraj says:

Hi Chandoo,

Thanks for the great code. I was wondering where in your code can we add Oscar's input?

Thanks,
Neeraj

16. Bob says:

I saved the John Walkenbach CellUsesLiteralValue code as an addin. It works fine when tested directly on a Excel 2010 worksheet. However, when I tried to use it on the same worksheet as a conditional formatting formula

for example, =CellUsesLiteralValue(C46)

I got this error message: This type of reference cannot be used in a Conditional Formatting formula. Anyone have thoughts on am I doing wrong?

17. Bob says:

Now I copied the code into a module in the workbook I have been testing. Now the conditional formatting works fine - no error message and formulas with literal values are highlighted. Having the code in the active workbook seems to be the only way to make this work - or am I missing something?

18. jimm says:

Thanks for the instructions, Chandoo. I'm a bit new to this, but have everything setup properly and it does not seem to be working. Did anyone else have any challenges followed by a break-through?

19. brian says:

I also cannot get this to work and am also new to this. I tried saving in personal workbook and using PERSONAL.XLSB! in conditional formatting formula and i was not allowed to use that type of formula. Chandoo were you able to use this functional saved in personal workbook without typing the prefix in the formula? It seems above you alluded to saving in personal but your formula does not use the personal prefix. I am simply trying to highlight an entire column with say 1,000 rows and look into all formulas at once for a hard coded value so that when I copy the column over to the next, I know which cells I (someone) made a manual adjustment to. Any help would be greatly appreciated as this is being done by me looking into each cell at the moment for a constant. thank you.

20. Jared Powell says:

Could this formula be modified to not highlight a cell if the only hardcoded number is a 1? would "1" just be added to the Operators?

21. Bhavik says:

I was thinking one liner can be used too.

'True means its a Formula
'False means not formula
Function IsFormulaBK(cell As Range) As Boolean
IsFormulaBK = Not (IIf(cell.formula Like "*[=^/*+-/()><, ]#*[0-9]*", True, False))
End Function

22. FreddyK says:

Thanks for sharing this tip. I tried using this, my only concern is that the function seems to return TRUE for a cell containing a formula that links to another worksheet in the same workbook, even though there is no constant within that formula. Any idea on how to fix this? I'd like to be able to identify constants only.

23. CHS says:

The conditional formatting doesn't work for single digit hard coded numbers. However, it does work for hard coded numbers with multiple digits.

Thanks!

24. Yury says:

Thanks, legend!

25. Yury says:

Hi mate

I get a false alarm with this code when a formula has a reference to the worksheet which has an ampersand in the name (eg 'A&B!'C2) - what happens - "&" get dropped then "'A" is giving the false true in the HasConstants code.

26. JOFOX says:

How about using the embedded Excel function isFormula()?

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.