# Find cells containing formulas with literal (hardcoded) values

This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from a macro that iterates through each worksheet in a given workbook looking for hardcoded values in formulas.

A link is generated and the formula itself is displayed if a hardcoded value is found.

#### What's on this webpage

## 1. Find hardcoded values in formulas (Conditional Formatting)

I found this UDF in David Hager's Excel Experts E-letter (EEE) on J Walkenbach's website. You need to apply conditional formatting with a formula that uses the User Defined Function described below.

Conditional Formatting highlights cells with formulas containing hardcoded values, the image above shows which cells contain hardcoded values. They are highlighted green. For example, cell C3 contains the following formula:

=SUM(A2:A5, 5)

The second argument in the SUM function is a hardcoded value, cell C3 is highlighted.

Note, Conditional Formatting is super-volatile and may slow down your workbook considerably. It only highlights cells, you need to find the highlighted cells yourself.

I recommend the macro in section 2. The macro creates a new worksheet and lists all formulas containing hardcoded values as well as links to those cells.

'Name User Defined Function Function CellUsesLiteralValue(Cell As Range) As Boolean 'Check if cell has not a formula If Not Cell.HasFormula Then 'Save boolean value FALSE to variable CellUsesLiteralValue CellUsesLiteralValue = False 'Continue here if cell has a formula Else 'Use like operator to determine if cell formula contains hardcoded values, it returns TRUE if found. 'Characters enclosed in brackets allows you to match any single character in the string. ' The hashtag matches any single digit, the asterisk matches zero or more characters CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()<>, ]#*" End If End Function

VBA: Range Formula property | Like Operator | HasFormula property | Declare Range object | If then statement

### 1.1 How can I locate cells containing formulas with literal values?

Use the UDF as your conditional formatting formula, I will explain exactly how in the next section below if you are interested. 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 that contains a literal numeric value.

You can test each cell in the range, and highlight it if the function returns True. The UDF with conditional formatting highlights cells containing formulas with literals, column C. You can get an excel file at the end of this post if you are interested in this technique.

I have bolded all literal values in the formulas, column E. It seems to work with nested formulas also. Boolean values can be expressed as TRUE/FALSE but also 1/0 in Excel. Note, some formulas are built to have hard-coded arguments, like col_index_num in the VLOOKUP function.

### 1.2 How to use the User Defined Function with Conditional Formatting

- Go to the worksheet you want to search.
- Press with left mouse button on the button containing a triangle to select all cells on the worksheet, see image above.
- Go to tab "Home" on the ribbon if you are not already there.
- Press with left mouse button on "Conditional Formatting" button, see image above. A pop-up menu appears.
- Press with left mouse button on "New Rule...", see image above. A dialog box shows up on the screen.

*The image above shows the wrong cell reference C2, it should be A1.* - Press with left mouse button on "Use a formula to determine which cells to format".
- Type following formula: =CellUsesLiteralValue(A1)
- Press with left mouse button on "Format..." button. Another dialog box appears.
- Go to tab "Fill".
- Pick a color.
- Press with left mouse button on "OK" button.
- Press with left mouse button on "OK" button again.

## 2. Find hardcoded values in formulas across worksheets

I have a large workbook and I am in a hurry!

The authors want you to use it as conditional formatting formula to spot cells containing formulas with literal values, that can be tedious work with a large workbook. I would like to show you how to use the udf to build a list of all cells containing literal values, from an entire workbook.

This picture above shows you what my macro below returns, first a new sheet is inserted. It is then populated with links to all cells containing formulas with literals and their corresponding formulas.

I have reused some parts of Rick Rothstein's macro.

'Name macro Sub FindLiteralsInWorkbook() 'Dimension variables and declare data types Dim C As Range, A As Range, Addresses As String, i As Single Dim cell As Range 'Add worksheet to workbook Sheets.Add 'The SET statement allows you to save an object reference to a variable, in this case the active worksheet Set x = ActiveSheet 'Save text "Link" to cell A1. x.Range("A1") = "Link" 'Save text "Formula" to cell B1. x.Range("B1") = "Formula" 'Save number 1 to variable i. i = 1 'Iterate through worksheets in active workbook For Each sh In ActiveWorkbook.Worksheets 'Enable error handling On Error Resume Next 'Find cells containing constants and save to object C Set C = sh.Cells.SpecialCells(xlConstants) 'Check if C is empty If C Is Nothing Then 'Find cells containing formulas and save to object C Set C = sh.Cells.SpecialCells(xlFormulas) 'Continue here if C is not empty Else 'Returns the union of the two cell ranges and saves to object C Set C = Union(C, sh.Cells.SpecialCells(xlFormulas)) End If 'Iterate through all the ranges in a multiple-area selection. For Each A In C.Areas 'Iterate through all cells in cell range A For Each cell In A 'Use User Defined Function to determine if cell contains at least one hardcoded value If CellUsesLiteralValue(cell) = True Then 'Add 1 to variable i i = i + 1 'Create a hyperlink based on the cell address x.Hyperlinks.Add Anchor:=x.Range("A" & i), _ Address:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, _ SubAddress:=sh.Name & "!" & cell.Address, _ TextToDisplay:=sh.Name & "!" & cell.Address x.Range("B" & i) = "'" & cell.Formula End If 'Continue with next cell Next cell 'Continue with next cell range Next A 'Disable error handling On Error GoTo 0 'Continue with next worksheet Next sh 'Resize column A:E widths x.Columns("A:E").AutoFit End Sub

VBA: SpecialCells(xlConstants) | Union | Areas

## 3. Where to put the code?

- Copy User Defined Function CellUsesLiteralValue.
- Go to the VB Editor (Alt + F11).
- Go to the menu and press with left mouse button on Insert.
- Press with left mouse button on Module.
- Paste UDF to your workbook's code module
- Copy macro FindLiteralsInWorkbook
- Paste to the module. Both the UDF and macro should now be visible in the module, see image above.
- Return to Excel.

## 4. How to run the macro

- Go to tab Developer on the ribbon.
- Press with left mouse button on "Macros" button.
- Press with left mouse button on "FindLiteralsInWorkbook" and then press with left mouse button on "Run".

### Recommended reading

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

Working with COMBO BOXES [Form Controls]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

How to change a picture in a worksheet dynamically [VBA]

Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

### 12 Responses to “Find cells containing formulas with literal (hardcoded) values”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Given that a Boolean defaults to False, you can simplify your CellUsesLiteralValue function like so...

Actually, if you use the single line form of If..Then, then you can reduce the code to a one-liner (the code area will probably line wrap the single line of code due to its length)...

Well, my previous attempt did not come out so well. I do not understand why, though, as I used the vb code tags as suggested just below the "Leave a Reply" heading. I don't know if this will work or not, but I am going to try. Here is the three-line code without any code tags...

Function CellUsesLiteralValue(Cell As Range) As Boolean

If Cell.HasFormula Then

CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"

End If

End Function

I just noticed in my second posting that the "End If" statement is missing.

That actually looks normal, so here is the one-liner version of it (remember, it might line wrap because of its length)...

Function CellUsesLiteralValue(Cell As Range) As Boolean

If Cell.HasFormula Then CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"

End Function

Rick Rothstein (MVP - Excel),

thank you for commenting.

Oscar, you had given me this formula to show items that are in Column B, but not in A.Array formula in cell C2:=INDEX($B$1:$B$5, MATCH(0, COUNTIF($C$1:C1, $B$1:$B$5)+COUNTIF($A$1:$A$5, $B$1:$B$5), 0)) + CTRL + SHIFT + ENTER. Copy C2 and paste it down as far as needed.

I added a condition to this formula :

C2:=INDEX($B$1:$B$11, MATCH(0,IF($E$1:$E$11="AB", COUNTIF($C$1:C1, $B$1:$B$11)+COUNTIF($A$1:$A$5, $B$1:$B$11)), 0))

Oscar, so column E is criteria for column B. It works for that one condition. I would like to add a condition for column A, but it's returning the wrong result. See below.

INDEX($B$1:$B$11, MATCH(0,IF(($E$1:$E$11="AB")* $F$1:$E$5="DB") , COUNTIF($C$1:C1, $B$1:$B$11)+COUNTIF($A$1:$A$5, $B$1:$B$11)), 0))

How would you do this?

Seán,

$F$1:$E$5 is a cell ref to to two columns, is this a typo?

Try this:

INDEX($B$1:$B$11, MATCH(0,IF(($E$1:$E$11="AB"), COUNTIF($C$1:C1, $B$1:$B$11)+IF($F$1:$E$5="DB",COUNTIF($A$1:$A$5, $B$1:$B$11),0)), 0))

That was a typo. Will that make a difference to the formula?

Oscar,

I'm comparing the A column to B.

=INDEX($A$1:$A$11, MATCH(0,IF(($B$1:$B$11="AB"),COUNTIF($E$1:E1, $A$1:$A$11)+IF($D$1:$D$11="DB",COUNTIF($C$1:$C$11, $A$1:$A$11),0)), 0))

A B C D

1 DX 1 MN

2 DX 2 MN

3 DX 3 MN

4 AB 4 MN

5 AB 5 DB

6 AB 7 DB

7 AB

Formula Result 4

6

Result should be 6

This is trickier than I thought.

Seán

Oscar it doesn't make sense that it's returning the incorrect result. You want to i loo return an array of numbers after using the if condition. r

Sean,

I don´t understand. Can you send me an example workbook?

https://www.get-digital-help.com/excel-consulting/

[…] https://www.get-digital-help.com/2015/01/19/find-cells-containing-formulas-with-literal-hard-coded-va… […]