Highlight Cells with Formulas in Excel

by

In our Excel work, we sometimes need to highlight cells that contain formulas.

This could be useful when you want to make sure you don’t accidentally delete any of the cells that contain formulas.

Or, you may want to highlight all cells that contain formulas when sharing them with other people so that they are also cautious and don’t tamper with these cells.

In this article, I will show you some simple techniques for highlighting cells containing formulas.

Method 1 – Using the ‘Go To Formulas’ Option to Highlight Cells with Formulas

The Excel Go To Formulas option is the quickest way to highlight cells with formulas. It allows us to select all the cells that have formulas, and then we can manually highlight them.

An example from a sheet used to calculate revenue is shown in the image below.

data set where we have formulas in some of the cells

We now need to highlight any cells that contain formulas. You can do so by following the steps below.

  1. Go to the “Home” tab.
click the home tab in the ribbon
  1. Expand the “Find & Select” options in the Editing Group.
click on the find and select option which is in the editing group
  1. Select “Formulas” to go to formulas.
from the drop down, click on the formulas option

When we click “Formulas,” Excel selects all cells that contain formulas. In this example, Excel selects the cells below.

all the cells that have formula in it are selected
  1. Select a Fill color to highlight the selected cells with formulas.
click on the fill color icon in the ribbon in the font group

All selected cells will highlight with the selected color.

all the cells that have formulas in it are highlighted in the specified color

Shortcut to Select All Cells with Formulas

You can select the cells with formulas using the below methods as well.

  1. Using the keyboard shortcut.
ALT + H + F +D + U
Also read: How to Select Visible Cells Only in Excel?

Method 2 – Using Conditional Formatting to Highlight Cells with Formulas in Excel

Although the “Go To Formulas” method is the simplest way to highlight cells with formulas, it has a big drawback.

It isn’t dynamic, which means that if you remove the formula from the cell or add a formula to other cells, the color won’t automatically update to reflect that.

Thankfully, you can get around this by using conditional formatting (in case you want the cells with formulas to get highlighted and updated automatically).

The image below shows an extract from a revenue calculation sheet.

data set that has formulas in cells that we want to highlight

Now, we need to highlight cells with formulas. It is possible that you will add new formulas to this sheet in the future.

According to the information given, conditional formatting is the best method for highlighting cells that contain formulas.

You can do so by following the steps below.

  1. Select any cell in your Excel sheet (in this case, I am selecting cell A1) and go to the “Home” tab.
click the home tab in the ribbon
  1. Expand the Conditional Formatting options and select the “New Rule…”.
in the conditional formatting drop down, click on the new rule option
  1. Select the last rule type ‘Use a formula to determine which cells to format‘ from the “New Formatting Rule” dialog box.
in the new formatting rule dialog box, select the user formula to determine which cells to format option
  1. Enter the below formula in the “Edit the Rule Description Box”.
=ISFORMULA(A1)

Please keep in mind that you have to use the same cell reference that you chose when you start the conditional formatting process.

Because I choose cell A1 first, I use A1 as the function’s cell reference. In other words, you have to type in reference to the cell that is currently active.

Be sure to use the cell reference as a relative reference if you are selecting the cell reference for the formula. For cell reference, avoid using dollar ($) signs.

Enter the isformula in the condition formatting formula field
  1. Click the “Format…” button to change the formatting of the cells with formulas.
click on the format button
  1. Apply the formatting and click the “OK” button.
select the color in which you want to highlight the cells that contain formulas
  1. Check the Preview of formatting (that’s the right formatting that you want to use) and click the “OK” button.
click the OK button in the new formatting rule dialog box
  1. Then open the “Conditional Formatting Rules Manager” dialog box. You can expand the “Conditional Formatting” of the Styles Group of the Home tab and select the “Manage Rules…”.
click on the manage rooms option in the conditional formatting dropdown
  1. Next, clear the “Applies to” box of the “Conditional Formatting Rules Manager” dialog box. 
select the applies to option
  1. By clicking the worksheet’s upper-left corner, you also may select the entire sheet. After that, you’ll see that the “Applies to” box has been updated to include the entire spreadsheet. Lastly, in the “Conditional Formatting Rules Manager” dialog box, click the “OK” button.
select the entire worksheet by clicking on the select all option at the top left of the worksheet

Now all the cells with formulas in your worksheet are highlighted.

all cells with formulas are now highlighted using conditional formatting

Even if you enter a formula later, it will be highlighted. For example, if we calculate the “Total No. of Units” in cell B10, that cell will get automatically highlighted.

adding a new formula would automatically give a color to the cell that contains the formula

Although this method to highlight cells with formulas works perfectly well in most cases, here is one thing you need to know – conditional formatting can make your Excel file slow. This can occur especially when there is a lot of data being formatted or when complex formulas are used with conditional formatting.

Also read: How to Select Non-adjacent Cells in Excel?

Method 3 – Use a VBA Code to Highlight Cells with Formulas in Excel

We sometimes need to highlight cells with formulas in multiple Excel spreadsheets.

In this situation, repeating the same process will tire you out. In that situation, recording the process and running a VBA code can sometimes be your best option.

The VBA code given below can be used to quickly highlight specific cells with formulas on the active sheet.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub FindFormulaCells()
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula() = True Then
cell.Interior.ColorIndex = 40
End If
Next cell
End Sub

The below image shows an extract of a revenue calculation sheet. 

data set that has the cells with formula that we want to highlight

Now, we need to highlight cells with formulas.

You can apply the below steps to apply the VBA code.

  1. Press ALT + F11 to open the VBA Editor.
open the VB editor
  1. Expand the “Insert” menu of the VBA Editor and select “Module”.
insert a new module
  1. Then, copy and paste the below VBA code on the new module.
' Code developed by Steve Scott from https://spreadsheetplanet.com

Sub FindFormulaCells()

    ' Iterate through each cell in the used range of the active sheet
    For Each cell In ActiveSheet.UsedRange
        
        ' Check if the current cell has a formula
        If cell.HasFormula() = True Then
            
            ' If the cell has a formula, set its interior color to ColorIndex 40 (light orange)
            cell.Interior.ColorIndex = 40
            
        End If
        
    ' Move to the next cell in the loop
    Next cell

End Sub
copy and paste the above VBA code into the module code window

In this example, I have used color code 40, which will give an orange color to the cells that have formulas in them. You can change it as per your preference.

  1. Click the “Run” button, which looks like a “Green” color triangle.
run the code by clicking on the green color triangle icon in the toolbar

Then, all the cells with formulas in our active Excel Worksheet will be highlighted as follows.

all that cells that have the formula are highlighted in the specified color

The above VBA code, however, does not automatically highlight subsequent formula cells.

To highlight new formula cells, you need to run the VBA code once again. To Save the VBA code, you have to save the Excel workbook as an Excel Macro-Enabled Workbook.

Also read: How to Highlight Blank Cells in Excel?

Explanation of the Code to Highlight Cells with Formulas

  1. Sub FindFormulaCells(): This line declares the beginning of a subroutine named “FindFormulaCells”.
  2. For Each cell In ActiveSheet.UsedRange: This line starts a “For Each” loop that iterates through each cell in the “UsedRange” of the “ActiveSheet”.
  3. If cell.HasFormula() = True Then: This line checks if the current cell in the loop has a formula. The “HasFormula” property of the cell object returns “True” if the cell contains a formula and “False” otherwise. If the cell contains a formula, the code execution proceeds to the next line.
  4. cell.Interior.ColorIndex = 40: If the cell contains a formula, this line sets the background color of the cell to a specific color. The “Interior” property of the cell object refers to the cell’s background, and “ColorIndex” is used to set the color. The value “40” corresponds to a shade of orange in Excel’s color palette.
  5. End If: This line marks the end of the “If” statement.
  6. Next cell: This line marks the end of the “For Each” loop. It tells the loop to move on to the next cell in the “UsedRange” and repeat the process.
  7. End Sub: This line marks the end of the “FindFormulaCells” subroutine.

In this article, you learned three different methods to highlight cells with formulas in Excel. You are free to use any of the ways listed above.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment