How To Highlight Cells Containing Specific Function In Google Sheet

highlight-cells-containing-specific-function-in-google-sheets

Have you ever wondered how to highlight cells containing specific function in Google Sheets to make them stand out from the rest of the cells?

You might have come across a Google Sheet where some columns are formatted differently than others. In fact, this type of formatting is necessary to highlight columns or rows that might be relatively more important for the intended audience. 

Table of Contents
  1. The Anatomy of FORMULATEXT Function in Google Sheets
  2. An Example of Using FORMULATEXT Function
  3. The Anatomy of REGEXMATCH Function in Google Sheets
  4. An Example of Using REGEXMATCH Function
  5. How to Highlight Cells Containing Specific Functions in Google Sheets: Step-by-Step

Therefore, in this article, we hope to teach you how to highlight cells with specific functions. We also aim to make this guide as simple as possible by adopting a divide-and-conquer approach. First, we will look at the two essential functions FORMULATEXT and REGEXMATCH. Then, we will see how these functions work together to highlight cells containing specific functions in Google Sheets.

We will be using our very own sales record Google Sheet throughout this tutorial. To get familiar with it, have a look at the image. You can see the sales of different products from January 2019 to March 2019.

highlight-cells-containing-specific-function-in-google-sheets

So how do we go about this?

To get a clear idea you need to learn about the two essential functions first and then move to the tutorial. At the end of the tutorial, we expect you will be able to perform the same steps for every function of your choice.

 

The Anatomy of FORMULATEXT Function in Google Sheets

The syntax (the way we write) of the FORMULATEXT function is as follows:

=FORMULATEXT(cell)

Let’s have a look at each part of the function to understand what is going on here:

  • = is the equal sign that starts off any function in Google Sheets.
  • FORMULATEXT is the name of our function.
  • cell refers to a single or a range of cells.

Note that if a range of cells is passed in FORMULATEXT, only the top leftmost cell is evaluated.

 

An Example of Using FORMULATEXT Function

We have seen the sales record Google Sheet above. A column “Demo” has been added to it. The value in each of its cells is an output of a formula. We need to view what formula has been used at each cell in column G. Therefore, we will use the FORMULATEXT function.

highlight-cells-containing-specific-function-in-google-sheets-1

To begin with it, we need another column at H. We will name it “Formula Text“. In the cell H2, we will enter the formula. The formula looks like this:

=FORMULATEXT(G2)

Once we enter the formula and press ENTER, it will then show us the actual formula present in the cell G2.

highlight-cells-containing-specific-function-in-google-sheets

We can also see a blue square icon in the bottom right of cell H2. We will right-click on it and drag it down to H5. This will then copy FORMULATEXT for the rest of the cells. Now, can you guess the output? Let’s see the output.

highlight-cells-containing-specific-function-in-google-sheets

We can observe what happens after we apply FORMULATEXT(cell) to column H with corresponding cells from G as the cell parameter. It returns the actual formula as text. Quite straightforward, isn’t it?

This simple problem can be practiced to perfection. Use the link below to get a copy of this problem set:

Make a copy of the example spreadsheet.

I assume the FORMULATEXT function is easier to understand. We now move on to our next function, REGEXMATCH.

 

The Anatomy of REGEXMATCH Function in Google Sheets

Before we dive into specifics of the REGEXMATCH function, we will try to know what regex is.

Regex

Regex stands for Regular Expression. It is a sequence of characters that defines a string pattern. Different algorithms use regex to find and match a string based on it. Beginners can go through the Regex tutorial to learn more. Google products use RE2 for regular expressions.

The syntax of the REGEXMATCH function is as follows:

=REGEXMATCH(text,regular_expression)
  • = is the equal sign that starts off any function in Google Sheets.
  • REGEXMATCH is the name of our function.
  • textis the parameter that needs to be matched by the algorithm against a regular expression. 
  • regular_expression defines a pattern.

 

An Example of Using REGEXMATCH Function

Let’s continue with our sales record sheet. We will add a “Random Text” column to it. This column contains some random text.

highlight-cells-containing-specific-function-in-google-sheets

We will then try to match the text in the Random Text against a regular expression. This example includes regular expressions from the table.

 

Regular Expression Description Example
[A-Za-z]+ Matches one or more occurrences of characters in the range (A-Z or a-z) My name is Jim.

Hello World.

Hope.
([0-9]-)+ Matches one or more occurrences of numbers separated by “-” 1-2-3

123-212

123-456-789
(<[A-Za-z]+>) Matches one or more groups of text tags

— Previous article

How to Use DELTA Function in Google Sheets

Next article —

How to Use EQ Function in Google Sheets

You May Also Like