Our Google Sheets file may contain several formulas spread across one Sheet. It’s easy to highlight all of them in one go.

But what about highlighting cells that contain a specific function in Google Sheets?

For example, you want to highlight all the cells containing the Vlookup function with one color, Sumif function with some other color, and so on.

In this tutorial, let’s learn how to highlight specific functions in Google Docs Sheets.

As a side note, we can use the ISFORMULA function to highlight all the cells containing formulas in one go.

Highlighting Cells Containing a Specific Function in Google Sheets

We can use operators for building formulas in Google Sheets. So let’s begin with highlighting some of them.

Related:- How to Sum, Multiply, Subtract, Divide Numbers in Google Sheets.

Conditional Format Addition Operator

An addition formula in Google Sheets is made up of an = (equal) sign followed by two or more numbers separated by the + (plus) sign.

Here is one example of a Google Sheets addition formula.

=5+15+30

or

=A1+B1+C1

How to highlight cells containing such specific formulas in Google Sheets?

Here, in the below example, I have additional formulas in the range D3:D5 and I3:I5.

The formula in cell D3, i.e., =A3+B3+C3, has been copied to the cells D4 and D5.

Similarly, the formula in cell I3, i.e., =F3+G3+H3, has been copied to the cells I4 and I5.

I have used the below custom rule to highlight the formulas in the said range in Google Sheets.

=regexmatch(FORMULATEXT(A1),"\+")

To apply this rule, first, select the range A1:I5.

Then enter the Regex formula in Format > Conditional formatting > Format Rule > Custom formula.

The above is an example of highlighting cells containing a specific function, here addition, in Google Sheets.

Understand the Custom Rule

Syntax:- REGEXMATCH(text, regular_expression)

Text – The FORMULATEXT function can return a formula from any cell as a text.

The Formulatext in the above rule checks all cells in A1:I5 (the selected range for conditional formatting) for a formula string.

Regular_Expression – To highlight cells containing a specific function, we must specify that function or operator in the regular_expression part of the formula. In our case, it is the + operator.

Note:- Some cells may contain text strings, i.e., Mango+Orange, which are not formulas. Such cells won’t get highlighted because the FORMULATEXT function would return N/A in that case.

Conditional Format Subtraction Operator

All the above points are applicable here. Only change the operator in the formula rule.

=regexmatch(FORMULATEXT(A1),"\-")

Conditional Format Division Operator

This custom rule is also in line with the above rules.

=regexmatch(FORMULATEXT(A1),"\/")

Here comes a question. What about multiple operators in a formula?

=A3+B3-C3

Please see this rule.

=regexmatch(FORMULATEXT(A1),"\-|\+")

Highlight SUMIF Function Cells in Google Sheets

To highlight cells containing a specific function, for example, SUMIF in Google Sheets, you can use the above custom rule with some modifications.

Here is the conditional format rule to highlight cells that contain the Sumif function in Google Sheets.

To apply, first, select the range and use this custom rule in conditional formatting.

=regexmatch(FORMULATEXT(A1),"(?i)sumif")

This formula highlights the cells G2 and G3 in my example.

Also, this is a case-insensitive Regex formula.

So even if you type the Sumif function in caps, lower, or proper case letters, it (the custom rule) can highlight those cells.

Highlight Cells that Contain Any Chosen Function in Google Sheets

To conditional format, the cells containing the VLOOKUP function, use this rule.

=regexmatch(FORMULATEXT(A1),"(?i)vlookup")

Similarly, you can replace the string in the expression, which is vlookup here, with any other function name.

You May Like:-

• How to Highlight Vlookup Result Value in Google Sheets.
• Highlight Intersecting Value in Google Sheets in a Two Way Lookup.