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.

Dataset with formulas that we want to highlight

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

Column C contains formulas

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
Click the Home tab
  1. In the Styles group, click on the Condition Formatting drop-down icon.
  2. Click on the New Rule icon in the options that show up
Click on New Conditional Formatting rule
  1. In the New Formatting Rule dialog box, click on the ‘Use a formula to determine which cells to format‘ option
Select use a formula to determine which cells to format option
  1. Enter the below formula in the field:
=ISFORMULA(A1)
Enter the formula
  1. Click the Format button
Click the Format button
  1. 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.
Choose the color to fill the cell
  1. Click OK
  2. 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).

Cells with formulas have been highlighted

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.

Dataset with formulas that we want to highlight

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
Select entire worksheet
  1. Press the F5 key on your keyboard. This will open the Go To dialog box.
  2. Click on the Special button. This will open the Go To Special dialog box.
Click the Special button

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.

  1. Select the Formula option in the Go To Special dialog box.
Click the formula tab
  1. 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).

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

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:

Dataset with formulas that we 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.
Click on visual basic icon in the ribbon
  1. Click the Insert option in the menu.
  2. Click on Module. This will insert a new module for the workbook.
Insert a new module
  1. 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
  1. Place the cursor anywhere in the code and press the F5 key (to run the macro)
  2. Click on the View Microsoft Excel icon in the menu (or press ALT + F11) to return to the worksheet.
click on the view Microsoft Excel icon

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

Cells with formula 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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment