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.
We now need to highlight any cells that contain formulas. You can do so by following the steps below.
- Go to the “Home” tab.
- Expand the “Find & Select” options in the Editing Group.
- Select “Formulas” to go to formulas.
When we click “Formulas,” Excel selects all cells that contain formulas. In this example, Excel selects the cells below.
- Select a Fill color to highlight the selected cells with formulas.
All selected cells will highlight with the selected color.
Shortcut to Select All Cells with Formulas
You can select the cells with formulas using the below methods as well.
- 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.
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.
- Select any cell in your Excel sheet (in this case, I am selecting cell A1) and go to the “Home” tab.
- Expand the Conditional Formatting options and select the “New Rule…”.
- Select the last rule type ‘Use a formula to determine which cells to format‘ from the “New Formatting Rule” dialog box.
- Enter the below formula in the “Edit the Rule Description Box”.
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.
- Click the “Format…” button to change the formatting of the cells with formulas.
- Apply the formatting and click the “OK” button.
- Check the Preview of formatting (that’s the right formatting that you want to use) and click the “OK” button.
- 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…”.
- Next, clear the “Applies to” box of the “Conditional Formatting Rules Manager” dialog box.
- 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.
Now all the cells with formulas in your worksheet are highlighted.
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.
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.
Now, we need to highlight cells with formulas.
You can apply the below steps to apply the VBA code.
- Press ALT + F11 to open the VBA Editor.
- Expand the “Insert” menu of the VBA Editor and select “Module”.
- 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
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.
- Click the “Run” button, which looks like a “Green” color triangle.
Then, all the cells with formulas in our active Excel Worksheet will be highlighted as follows.
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
Sub FindFormulaCells(): This line declares the beginning of a subroutine named “FindFormulaCells”.
For Each cell In ActiveSheet.UsedRange: This line starts a “For Each” loop that iterates through each cell in the “UsedRange” of the “ActiveSheet”.
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.
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.
End If: This line marks the end of the “If” statement.
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.
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: