Categories
Excel

Highlighting Formula Cells with Conditional Formatting

The IsFormula returns TRUE if the reference cell houses a formula and FALSE if it does not. You can use this Boolean result in conjunction with conditional formatting so that all formulas are highlighted automatically in a format of your choice.

  1. Handling IsFormula Function Errors
  2. Creating IsFormula Function for Previous Versions

One of the best things about using conditional formatting is that your spreadsheet’s formula identification capabilities will be dynamic. This means that if you add or remove a formula, your formatting will change accordingly. Here we explain how to do this.

  1. Select a range of cells on your spreadsheet, e.g. A1:J50.
    Avoid the temptation of selecting an entire worksheet, as this can add unnecessary overhead to your spreadsheet.
  2. With these cells selected, and with A1 the active cell of the selection, go to the Home tab, in the Styles group, and click Conditional Formatting.
  3. Click New Rule.
  4. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
  5. In the Format values where this formula is true box, enter =ISFORMULA(A1), where A1 is the first cell in your selected range.
  6. Click the Format button and choose any formatting you want to use to identify formula cells.
  7. Click OK, then OK again.

At this point, the specified formula should be applied to all cells on your worksheet that contain formulas. If you delete or overtype a cell containing a formula, the conditional formatting will disappear. Similarly, if you enter a new formula into any cell within the range, it too will be highlighted.

This simple conditional formatting can make your spreadsheets a lot easier to deal with when it comes time to maintain or modify them.

IsFormula Function Returns #NAME! Error

This error occurs when Excel does not recognize the function name or the syntax of the formula. To fix this error, you need to make sure that the function name is spelled correctly, and that the formula follows the proper syntax rules.

This function is available in Excel 2013 and later versions. If you use this function in earlier versions of Excel, you may encounter compatibility issues, such as #VALUE! or #NAME! errors. To avoid these issues, you can use the VBA code in this custom function (also called a user-defined function).

Creating IsFormula Function (User Defined Function)

Previous Excel versions do not have a built-in function that identifies formulas. Once a formula is entered into a cell, you can tell whether the cell is a static value or a value derived from a formula only by clicking on each cell and looking in the Formula bar. This tutorial fills that gap with a custom function.

The VBA code in this custom function (also called a user-defined function) enables you to identify cells that contain formulas without having to click through 10,000 cells and examine each one.

  1. Press Alt+F11 (or Options+F11) to open Visual Basic Editor.
  2. Click Insert > Module.
  3. Enter the following function:
Function IsFormula(Check_Cell As Range)
    IsFormula = Check_Cell.HasFormula
End Function

Close the window. Now this function is available in any cell on any worksheet in this workbook when you enter the formula =IsFormula($A$1).

You can also use conditional formatting to highlight formula cells in a different color, font, or border style as we described earlier in this tutorial.

Conditional Formatting

  1. Setting Up Checkboxes for Conditional Formatting
  2. Highlighting Formula Cells with Conditional Formatting
  3. Sum Cells That Meet Conditional Formatting Criteria
  4. Highlight Every Other Row or Column with MOD Function and Conditional Formatting
  5. Enable and Disable Conditional Formatting with a Checkbox
  6. Sort Data By Conditional Formatting Criteria