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

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

What about nested functions?

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.

Also, please share your observations in the comments so that we can improve this code.

Download Example Workbook

Click here to download this VBA code. After downloading the file, go to Module 1 (press ALT+F11) to see the code. Copy it or modify it as you see fit.

Your comments please?

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:

Check out below articles to learn more about how to audit spreadsheets and prevent risk of miscalculation:

• Spreadsheet risk management – 4 part series
• Show all names & references
• Go to special, a powerful way to navigate your workbooks