Excel tutorials

Highlight all cells that contain a formula instead of value or text

If you need to know which cells on the worksheet contain the formula, then you can use Conditional Formatting. In the picture below we have the following situation. In the range of 'A1:D10' cells there are values. Our goal is to highlight the color of each cell in which the formula is located. Those cells that contain only the value, number, or text should remain unmarked.

Color to identify each cell containing the formula

In the picture below, notice the actual situation when we include the representation of formula in the cells on the worksheet in Excel. If you want to display formulas instead of values in cells, then do the following: On the 'Formulas' tab in the 'Formula Auditing' command group, click 'Show Formula'.

 

View formula in cells on the Excel worksheet

If you want the color to mark each cell that contains a formula you have two options.

  1. Create the named formula
  2. Create UDF Function (User Defined Function)

1. How to highlight the cell containing the formula using the named formula

The first example refers to the creation of a named formula, which you place in the Excel Name Manager.

These two formulas below return the same result:

On the 'Formulas' tab, click 'Name Manager' and create a new name item.

name: IsFormula
refers to: =ISFORMULA(INDIRECT("rc";FALSE))

or

name: IsFormula
refers to: =GET.CELL(48;INDIRECT("rc";FALSE)))

When you create a new item, it should look like the image below.

Create a new name in the Name Manager

After creating and naming a new formula, your Name Manager looks like the picture below.

Created items in Excel Name Manager

1a. Set color to highlight the cell background that contains the formula

The next step is to select the range of cells in which the data is located, on the worksheet. In this case, the range is 'A1:D10'. Start Conditional Formatting and set the color to highlight each cell that you want to point out if the cell contains a formula instead of the values. Select items to set the "Use a formula to determine which cells to format" rule and enter the formula =IsFormula(A1). Also, set the color format for a prominent cell containing the formula. It looks like the picture below.

Starting conditional formatting and setting the background color of cells

After setting the format to highlight the background cells color of your 'Conditional Formatting Rules Manager "looks like this in the picture below.

Conditional Formatting in Excel

Finally, after finishing the rules and naming formula, note (the first image of this tutorial) that Excel has highlighted all the cells that contain the formula.

2. How to highlight a cell containing the formula using UDF function

Another way to highlight the background of cells containing formulas is to use a VBA macro, ie. UDF function (User definition Function).

If you want to try one of the UDF listed below, copy it to your VBE for Excel by inserting the new Module and pasting the source code. Select the entire cell range, run 'Conditional Formatting' and set this expression =IsFormula(A1) into the rule field.

Thus the UDF prominent cells containing the formula are the following:

UDF function 1.

Function IsFormula(cell) As Boolean
'Excel 2013 and new
IsFormula = cell.HasFormula
End Function

UDF function 2.

Function IsFormula(cell) As Boolean
'Excel 2013 and new
If rr.HasFormula = True Then
MsgBox "Attention this cell contain formula!"
End If
End Function

UDF function 3.

Function IsFormula(ByVal Ref As Range) As Variant
If Ref.Cells.Count > 1 Then
IsFormula = CVErr(xlErrNA)
Else
IsFormula = Ref.HasFormula
End If
End Function

NOTE!
A workbook containing the formulas listed here or Excel UDF functions should be saved in the format: *.xls or *.xslm or *.xlsb

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.