Excel Magic: How To Highlight Cells With Formula

Do you spend hours manually highlighting cells in Excel? Are you tired of doing it the old-fashioned way? Highlighting cells with formulas can be a daunting task, especially when you have a large spreadsheet. 

Luckily, Excel has several built-in features that allow you to highlight cells with formulas in just a few clicks.

In this article, you will learn how to highlight cells with formulas in Excel. I’ll show you step-by-step how to use two methods to highlight cells that contain formulas.

This feature will not only save you time but also help you identify errors in your spreadsheet. So, whether you’re a beginner or an advanced user, this article is for you. Let’s get going!

Two Methods To Highlight Cells With Formulas

If you’re working with a complex Excel workbook, it can be challenging to keep track of all the formulas and their corresponding cells.

Highlighting cells with formulas can help you quickly identify and analyze important data along with identifying inconsistent formulas. 

In this section, we’ll explore two ways to highlight cells with formulas in Excel.

Using the Go To Special Feature

One way to highlight cells with formulas is to use the Go To Special feature. I tend to use this when I’m only looking for a quick one-off analysis solution.

Here’s how to do it:

  1. Highlight the range of cells you want to highlight / format
  2. Click the “Home” tab, and then click the “Find & Select” button under the Editing group
  3. Select Go To Special. The keyboard shortcut for steps 2 and 3 is to hit F5 and click Special in the Go To dialog box
  4. In the “Go To Special” dialog box, select “Formulas”, and then click OK
  5. The cells with formulas will be selected. Now, you can apply formatting to these cells
Go To Special Formula

Conditional Formatting

Another way to highlight cells with formulas is to use conditional formatting. This method is more dynamic and will automatically update the formatting if you insert a formula in that range.

Here’s how to do it:

  1. Highlight the range of cells you want to highlight / format
  2. Click the “Home” tab, and then click the “Conditional Formatting” button in the Styles group
  3. Select New Rule
  4. In the “New Formatting Rule” dialog box, select “Use A Formula…” to determine which cells to format
  5. Enter the formula =ISFORMULA(A1), where A1 is the first cell in the selected range
  6. Click the Format button, and then select the formatting you want to apply to the cells with formulas
  7. Click “OK” to close the New Formatting Rule dialog box
Conditional Formatting dialog

With conditional formatting, you can highlight cells with formulas in a specific color or apply other formatting options. Depending on what formula you use, you can also use this feature to highlight cells that reference other cells, cells that contain specific text or values, and more.

Careful not to overkill it though as conditional formatting can impact Excel speeds.

Advanced Techniques To Highlight Cells With Formulas

Using VBA To Highlight Cells

If you’re comfortable with VBA, you can use it to create a more advanced highlighting system. VBA allows you to automate repetitive tasks and create custom solutions tailored to your specific needs. Save this down in your personal macro workbook if you find yourself using this quite often.

Here’s the “normal” language of what we are trying to achieve with VBA. Loop through all the cells in a range and check if they contain a formula. If a cell contains a formula, the macro can apply specific formatting to that cell.

Here’s an example of VBA code that highlights cells with formulas in yellow:

Sub HighlightFormulaCells()

    Dim myRange As Range

    Dim cell As Range

    Set myRange = ActiveSheet.UsedRange

    For Each cell In myRange

        If cell.HasFormula Then

            cell.Interior.ColorIndex = 6

        End If

    Next cell

End Sub

To use this code, open the Visual Basic Editor in Excel and create a new module. Paste the code into the module and run the macro.

Creating Custom Functions

Another advanced technique for highlighting cells with formulas is to create custom functions. Custom functions allow you to extend the functionality of Excel by creating your own formulas.

To create a custom function that highlights cells with formulas, you can use the same logic as the VBA macro. The function would loop through a range of cells and apply formatting to cells that contain formulas.

Here’s an example of a custom function that highlights cells with formulas in yellow:

Function HighlightFormulaCells(rng As Range)

    Dim cell As Range

    For Each cell In rng

        If cell.HasFormula Then

            cell.Interior.ColorIndex = 6

        End If

    Next cell

End Function

To use this function, open the Visual Basic Editor in Excel and create a new module. Paste the code into the module and save the file as an add-in.

You can then use the function in any Excel worksheet by typing “=HighlightFormulaCells(A1:C10)” (replacing A1:C10 with the range you want to highlight).

Additional Tips And Tricks

Finally, here are a few extra tips and tricks for highlighting cells with formulas in Excel.

Highlight Cells That Do Not Contain Formulas In Excel

If you want to highlight cells that do not contain formulas, you can use the formula “=NOT(ISFORMULA(A1))” in conditional formatting. Update cell A1 accordingly.

This formula checks if the cell in A1 does not contain a formula and applies the formatting accordingly.

Highlight Cells That Contain Specific Formulas In Excel

If you want to highlight cells that contains the formula “=SUMPRODUCT(A1:A10,B1:B10)”, you can use the formula “=FORMULATEXT(A1)=”=SUMPRODUCT(A1:A10,B1:B10)”” in conditional formatting.

This formula checks if the formula in cell A1 is exactly “=SUMPRODUCT(A1:A10,B1:B10)” and applies the formatting accordingly. 

Applying Formulas to Multiple Cells

This isn’t a highlighting tip per se, but you can also apply formulas to multiple cells at once. This can save you time when working with large datasets.

Use similar steps to “highlight all the formulas” before you format them:

  1. Use steps 1-4 of the Go To Special example
  2. Type the formula into the first cell
  3. Press “Ctrl + Enter” to apply the formula to all selected cells.

This a quick and easy way to update all the formulas in your range.

Thanks for reading and I hope you found this helpful!

You may also be interested in:

Leave a Comment