Highlight cells that contains Formula in Excel

This hack is very useful if you often need to change or check your formulas in your worksheet. To achieve the goal we will use Excel’s conditional formatting and VBA module.

  • First open your worksheet and go to the Visual Basic editor(Alt+F11).
  • Now right click on the VBA project explorer, click Insert and click Module.
    Insert Module in Excel VBA
  • Right down this function there
    Function HighlightFormula(range) As Boolean
    HighlightFormula = range.HasFormula
    End Function
    

    Save and close VBA editor

  • Select the range you want to use the conditional formatting
  • On Excel’s window click on the Conditional Formatting >> New Rule…
  • Select Use a formula to determine which cell to format.
  • Enter formula on the formula-bar there, set the formatting as you want and click OK
    Conditional formatting for finding formulas in Excel

Now see the output
conditionally formatted cells in excel

Add a Comment

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.