Highlight Cells With Formulas in Excel

In many situations, you may want to quickly highlight all the cells with formulas in Excel.

When I was working as a financial analyst a few years ago, this is something I used to do often to mark the cells that contain formulas.

Another situation where this may be useful is when you want to share your file with a client or a colleague, and you do not want them to make changes to cells with formulas.

So you can highlight the cells that contain formulas, which also act as a visual indicator to be extra cautious with these cells.

Other times, you may want to quickly highlight cells that contain formulas so that you can get rid of any additional formulas that you do not need in your workbook.

In this short tutorial, I’m going to show you three straightforward ways you can use to quickly highlight cells with formulas in Excel.

Using Conditional Formatting

The best way to highlight sales with formulas would be by using conditional formatting.

Conditional Formatting can be configured to check each cell in a given range (or the entire worksheet) and highlight only those cells that have a formula in them.

Let me show you how this works with a simple example.

Below I have an example data set where I have the Sales Rep name in column A, the sales they have made in column B, and the commission that they have earned in column C.

The commission column (i.e., column C) is calculated based on the sales value and contains a formula.

Here are the steps to use conditional formatting to highlight all the cells that contain a formula:

1. Select the entire data set or the entire worksheet in which you want to highlight the cells with formulas
2. Click the Home tab

3. In the Styles group, click on the Condition Formatting drop-down icon.
4. Click on the New Rule icon in the options that show up

5. In the New Formatting Rule dialog box, click on the ‘Use a formula to determine which cells to format‘ option

6. Enter the below formula in the field:

=ISFORMULA(A1)

7. Click the Format button

8. In the ‘Format Cells’ dialog box that opens, select the color with which you want to highlight the cells that have the formulas. In this example, I’ll go with the green color.

9. Click OK
10. Click OK

As soon as you do the above steps, conditional formatting will instantly highlight all the cells that contain a formula (as shown below).

I have used ISFORMULA in conditional formatting to check whether a cell has a formula or not. If it does, ISFORMULA returns TRUE, and the cell gets highlighted with the specified color.

One good thing about condition formatting is that this highlighting is dynamic, which means that in case you remove the formula from a cell, the cell will stop being highlighted. And on the other hand, if you add a formula to any cell (in the range that has the conditional formatting applied to it), it would instantly get highlighted.

Caution: One small thing to remember when using conditional formatting is that it’s volatile and can slow down your workbook. While the performance impact is negligible when it’s applied to a small data set, it could lead to a little bit of lag when it’s applied to a large data set in a worksheet or on multiple worksheets in your workbook.

Also read: How to Lock Formulas in Excel

Using the Go-To Option

Another quick way to highlight cells that contain formulas is by using the Go-To option in Excel.

With the Go To option, you can select all the cells that contain formulas, and once you have them selected, you can manually highlight them.

Below I have the same data set where I have the names in column A, sales values in column B, and commission values that are calculated using a formula in column C.

Here are the steps to highlight all the cells containing formulas:

1. Select the range in which you want to select the cells that have the formula. If you want to select the entire worksheet, you can click on the gray triangle at the top left part of the worksheet or use the keyboard shortcut Control + A + A

2. Press the F5 key on your keyboard. This will open the Go To dialog box.
3. Click on the Special button. This will open the Go To Special dialog box.

You can also open the Go To Special dialog box by clicking on the Home tab, and then clicking on the Find and Select option (in the Editing group), and then clicking on the Go To Special option.

4. Select the Formula option in the Go To Special dialog box.

5. Click OK

The above steps would select all the cells that contain a formula in the selected range (the one that we selected in Step 1).

6. Fill color in the selected cells. You can do this by clicking on the Home tab and then using the Fill Color option.

Unlike the Conditional Formatting method, this one is not dynamic.

This means that in case you remove formulas from any cell over adding any formula in another cell, the highlight will not automatically update.

Also read: How to Hide Formulas in Excel (and Only Display the Value)

Using VBA To Highlight Cells with Formulas

If this is something that you need to do on a regular basis, you can consider using a simple VBA code to do this.

One benefit of using VBA is that you can set it up once and then reuse it easily in the same workbook.

You can also save the VBA code in your Personal Macro Workbook so that you can use it on any Excel workbook on your system.

Let me show you how it works.

Below have the same data set where I have formulas in column C that I want to highlight:

And here are the steps to highlight cells with formulas using VBA:

1. Select the range of cells where you want to highlight the cells
2. Click the Developer tab and then click on the Visual Basic icon. This will open the VB Editor for the workbook.

3. Click the Insert option in the menu.
4. Click on Module. This will insert a new module for the workbook.

5. Copy and paste the below VBA code into the module code window.

'Code developed by Sumit Bansal from https://trumpexcel.com

Sub HighlightCellsWithFormulas()

    Dim rng As Range
    Dim cell As Range
    

    Set rng = Selection
    
    ' Loop through each cell in the range
    For Each cell In rng
    
        ' Check if the cell contains a formula
        If cell.HasFormula Then
        
            ' Highlight the cell with yellow background color, change RGB values as desired
            cell.Interior.Color = RGB(255, 255, 0)
            
        End If
        
    Next cell

End Sub

6. Place the cursor anywhere in the code and press the F5 key (to run the macro)
7. Click on the View Microsoft Excel icon in the menu (or press ALT + F11) to return to the worksheet.

You will see that the cells with formulas have been highlighted in yellow color.

The above VBA code goes through each cell in the selection and then identifies those cells that have a formula. Once it has identified these cells, it applies the specified fill color to it (which is yellow in this case).

Remember that the changes done by the VBA code are not reversible. So it’s always a good idea to create a backup copy of your dataset before running the VBA code.

Note: If you want to reuse this code in the workbook, you will have to save your file as a macro-enabled Excel workbook with a .xlsm extension

Pro Tip: If you add this code to your Personal Macro Workbook (click here to learn how) and then add an icon to run this code in the Quick Access Toolbar (QAT), you will then be able to access this code in any of your Excel files on your system with a single click on the QAT

So these are three straightforward methods you can use to quickly highlight all the cells that contain formulas in Excel.

Personally, I prefer using the conditional formatting method as it dynamically identifies cells with formulas while I’m creating data models in Excel.

Many people also prefer the VB method as it only has a one-time setup and can be reused easily.

Other Excel articles you may also like:

• Formula vs Function in Excel – What’s the Difference?
• How to Highlight Blank Cells in Excel
• Remove Formulas (but Keep Data) in Excel
• Highlight Alternate Rows in Excel
• How to Count Cells that Contain Text Strings
• How to Copy and Paste Formulas in Excel without Changing Cell References