Skip to Content

Google Sheets Conditional Formatting Custom Formula (6 Examples)

In this guide, you’ll learn how to create and use any conditional formatting custom formula in Google sheets.

The preset conditional formatting rules are very useful and versatile.

However, it’s just not quite useful and versatile enough sometimes.

Fortunately, you can extend its versatility by writing Google Sheets conditional formatting custom formulas.

Let’s get into it.

Why use a custom formula for Conditional formatting?

The reason why you may need to use a conditional formatting custom formula is to have full control of the conditions that trigger a rule and apply the exact logic you need.

Even though Google Sheets has a large number of presets, they are not complex enough to fit the needs of every user.

That’s why the custom formula is there for you to create more advanced rules.

How to create Conditional formatting custom formula in Google Sheets

Let’s take a look at some of the steps you may follow to enter your own custom conditional formatting formulas and functions.

Then, we’ll look at some real-world examples that will help you understand how custom formulas work.

To access and use Google Sheets conditional formatting custom formula, first, select the cells to be examined and formatted. Next, go to Format > Conditional formatting. Under the Format rules, select “Custom formula is” from the list. Specify your custom formula in the given box and set your formatting. Then click Done.

The above is just a summary of the entire process.

Obey the below step-by-step guide:

Step 1: Select the Cell or Cells to be formatted

This step is easy.

Using your mouse pointer, simply click and drag over all the cells you want the conditional formatting to affect.

Step 2: Go to Format > Conditional formatting

Another simple step here.

Move your cursor to your Google sheets Menus and click on the Format menu.

This action will reveal all the items under the Format menu including Conditional formatting. Simply click on it to open the pane on the right.

Opening Conditional formatting in Google sheets

Step 3: Under the Format rules, click on the drop-down and select “Custom formula is” from the list.

Entring Custom formula in Google Sheets conditional formatting

Step 4: Enter your Custom formula

At this stage, you’ll be presented with a text box where you can enter the rule as a formula.

We’ll soon explain some formulas in the examples.

Step 5: Set your formatting and click Done

Once you put in your formula, make the necessary formatting and click on the Done button to complete the setup.

These are the necessary steps you need to use custom conditional formatting formulas in Google Sheets.

Let’s take a look at some examples.

Examples of Conditional formatting Custom formulas in Google Sheets

The examples we’ll address will describe how to create conditional formatting custom formulas to do stuff that is impossible with the preset rules.

We’ll look at formulas that will:

  • Highlight text entries in cells (Example 1).
  • Highlight rows based on a value (Example 2)
  • Highlight dates that fall on a weekend (Example 3)
  • Highlight Dates in the next 15 days (Example 4)
  • Highlight missing values in two data sets (Example 5)
  • Highlight Laptops with at least 16GB RAM under $1000 (Example 6)

I hope that some of these formulas may prove useful to you. If not, I’m sure they will inspire you to create your own conditional formatting formulas in Google Sheets.

Example 1: Conditional formatting formula to highlight cells with text entries

In this example, we’ll create a conditional formatting custom formula that highlights cells in range B3:D14 only if the cell contains text entries.

As seen in the screenshot below, the rule is set to highlight cells that contain text entries and leave cells with numbers unformatted.

=ISTEXT(B3)

Google Sheets Conditional formatting rule to highlight cells containing text

None of the conditional formattings presets in Google Sheets can do this task. And this is where a custom formula comes in handy.

With a custom conditional formatting formula in Google Sheets, you can create a formula that will return TRUE if the cell contains text and return FALSE if otherwise.

Note: Conditional formatting custom formulas must be logical, with either TRUE or FALSE as a return value. If the formula returns TRUE, the condition is met, and conditional formatting is applied. The conditional formatting is not applied if the formula returns FALSE. If your formula doesn’t return TRUE or FALSE, it can work as a trigger for conditional formatting.

Obey the below steps:

Step 1: Select the Cell or Cells to be formatted

Using your mouse pointer, simply click and drag over all the cells you want the conditional formatting to affect.

In this example, we’ll select B3:D14.

Step 2: Go to Format > Conditional formatting

Move your cursor to your Google sheets Menus and click on the Format menu.

This action will reveal all the items under the Format menu including Conditional formatting.

Simply click on it and it’ll open immediately.

Opening Conditional formatting in Google sheets

Step 3: Under the Format rules, click on the drop-down and select “Custom formula is” from the list.

Entring Custom formula in Google Sheets conditional formatting

Step 4: Enter this formula: =ISTEXT(B3)

You’ll be presented with a text box to enter your custom formula, enter =ISTEXT(B3)

Step 5: Set the formatting

There are a lot of formatting styles you can choose for the cells when they meet the condition you set.

This can be done using the font controls under the Formatting style.

See the screenshot below:

formatting style menu on the Conditional formatting pane

There are several preset formatting you can choose from (including green background, yellow background, red background, green text, yellow text, and red text)

Click on the Default button under the Formatting style to reveal the presets.

Otherwise, you can define your own format styles using the font controls.

Step 6: Click Done

Once you hit on the done button, all the cells containing text will be highlighted.

Understanding Relative and Absolute references when creating conditional formatting formulas in Google Sheets

I’d like you to have a good understanding of Relative and Absolute cell referencing before we move on to the rest of the examples.

If you are already familiar with these two concepts, you can proceed to the next example though.

Otherwise, you’d need to have some knowledge about it to be able to better understand and create your own conditional formatting custom formulas.

Cell references will most of the time appear in conditional formatting formulas. Depending on the rule you want to set, this cell reference can be either relative or absolute.

The formula entered in Example 1 contains a relative reference to the upper-left cell in the selected range.

When entering a conditional formatting formula for a range of cells, you’ll typically refer to the active cell, which is usually the upper-left cell in the selected range, except you are referring to one specific cell.

For instance, let’s say that you select range B3:D14, and you want to apply conditional formatting to all cells in the range that exceed the value in another cell, say cell A1. You should enter this conditional formatting formula:

=B3>$A$1

The reference to cell A1, in this case, is an absolute reference that will not be adjusted for the cells in the selected range. To put it another way, the conditional formatting custom formula in cell B4 will look something like this:

=B4>$A$1

The relative cell reference will continue to adjust for each cell in the range, but the absolute cell reference will not.

Example 2: Conditional formatting formula to Highlight rows based on a value

When working with spreadsheets, you will almost certainly encounter a situation in which you need to highlight an entire row or column of data if certain conditions are met.

A Google Sheets spreadsheet with a conditional formatting custom formula is shown in the screenshot below. If one of the months selected in Cell C2 appears in the first column, the entire row for that month is highlighted.

Click here to learn how to create a drop-down list in Google Sheets. It’s a complete guide that will help you enhance your spreadsheet skills.

=$A5=$C$2

Example of Google sheet's conditional formatting based on another cell

Follow the steps below to create a conditional formatting formula that highlights a row based on a value you enter.

Step 1: Select the range of cells for the Conditional Formatting

Step 2: Go to Format > Conditional formatting

Step 3: Enter this Conditional Formatting Formula: =$A5=$C$2

To enter the formula, first, under the Format rules, click on the drop-down. Then select “Custom formula is” from the list.

You will then be presented with a text box in which to enter your custom formula, enter =$A5=$C$2 or =$A5=”Month” if you are not using cell referencing.

Then set the formatting you want to apply to cells that meet this condition.

Step 4: Click Done

When you’ve completed all of the steps correctly, your spreadsheet should be able to highlight rows based on the value you’ve chosen.

For cell A5, a mix reference is used to make the column part of the reference absolute; thus, the comparison is always performed using the contents of column A.

Example 3: Conditional formatting formula to Highlight dates that fall on a weekend

There are few conditional formatting rules that deal with dates in Google sheets.

But none of these rules let you identify dates that fall on a weekend.

This example will show you a formula that highlights weekend dates.

=OR(WEEKDAY(A3)=7,WEEKDAY(A3)=1)

Google Sheets Conditional formatting rule dates occuring on weekends

Step 1: Select the range of cells for the Conditional Formatting

In this example, you should select all the cells that contain the dates.

Step 2: Go to Format > Conditional formatting

Step 3: Enter this Conditional Formatting Custom Formula: =OR(WEEKDAY(A3)=7,WEEKDAY(A3)=1)

To enter the formula, first, under the Format rules, click on the drop-down. Then select “Custom formula is” from the list.

You will then be presented with a text box in which to enter your custom formula, enter =OR(WEEKDAY(A3)=7,WEEKDAY(A3)=1)

Then set the formatting you want to apply to the dates that fall on weekends.

Step 4: Click Done

When you’ve completed all of the steps correctly, your spreadsheet should be able to highlight cells with weekend dates.

Example 4: Highlight Dates in the next 15 days

This example has a bunch of dates for events mixed up in a range of cells.

We want to create a conditional formatting custom formula that highlights all the dates occurring in the next 15 days.

The formula should be smart enough to make sure that dates are in the future and 15 days or less from today’s date.

As show below, one of the simplest ways to do this is to make use of both the AND function and the NOW function.

=AND(A4>NOW(),A4<=(NOW()+30))

With the current date being 18th August 2021, the conditional formatting highlights weekend dates as follows:

Google Sheets Conditional formatting rule to highlight dates occuring in the next 15 days.

The steps:

Step 1: Select the range of cells for the Conditional Formatting.

Step 2: Go to Format > Conditional formatting.

Step 3: Select “Custom formula is” under Format rules.

Step 4: Enter this Conditional Formatting Formula: =AND(A4>NOW(),A4<=(NOW()+30))

Step 5: Set the formatting under Formatting Style

Step 6: Click Done

Example 5: Conditional formatting formula to highlight missing values

Assuming you have two sets of data (Data A & Data B) in your spreadsheet and you want to highlight the values in one data that are missing from another.

This is possible with the help of the =COUNTIF function as stated below:

=COUNTIF(D2:E15,A2)=0

Google Sheets Conditional formatting rule to highlight missing values

This formula simply checks each value in Data 1 (A2:B15) against values in Data 2 (D2:E15). When the count equals zero, the formula returns TRUE and the rule is triggered to highlight values in Data 1 that are missing in Data 2.

The steps:

Step 1: Select the range of cells for the Conditional Formatting.

Step 2: Go to Format > Conditional formatting.

Step 3: Select “Custom formula is” under Format rules.

Step 4: Enter this Conditional Formatting Formula: =COUNTIF(D2:E15,A2)=0

Step 5: Set the formatting under Formatting Style

Step 6: Click Done

Example 6: Formula to Highlight Laptops with at least 16GB RAM under $1000

Using the spreadsheet below, you can write a conditional formatting custom formula to find Laptops that have at least 16GB RAM under $1000.

Use the formula below to make this rule:

=AND($B4<1000,$C4>=16)

Google Sheets Conditional formatting rule to highlight items with low price

In this example, the dollar signs ($) lock the reference to columns B and C, whilst the AND function makes sure both conditions are TRUE. In rows where the formula returns TRUE, the formatting is triggered.

The steps:

Step 1: Select the range of cells for the Conditional Formatting.

Step 2: Go to Format > Conditional formatting.

Step 3: Select “Custom formula is” under Format rules.

Step 4: Enter this Conditional Formatting Formula: =AND($B4<1000,$C4>=16)

Step 5: Set the formatting under Formatting Style

Step 6: Click Done

Troubleshooting conditional formatting formulas in Google Sheets

When working with conditional formatting custom formulas, it’s very likely that you’ll finish writing them only to discover that they have no effect on your data. Most likely, there is an error in your formula.

The solution may be as simple as beginning the formula with an equal sign (=).

Without an equal sign (=) preceding your formula, Google Sheets will silently treat it as text, rendering it ineffective.

To correct this, simply begin the formula with an equal (=) sign.

However, if nothing appears to be wrong with your formula but it still fails to trigger the conditional formatting rule, you may need to dig a little deeper by testing the formula directly inside the spreadsheet. This allows you to see exactly what’s going on.

When you directly enter the formula into your spreadsheet and it returns TRUE or FALSE, it means that they are working fine, but the rule you set was not triggered.

However, if you get some errors or it doesn’t return TRUE or FALSE after entering your formula, it is an indication that something is wrong with the formula and you should consider rewriting it.

Limitations of Conditional formatting Formulas

So far, I’ve identified one limitation with formula-based conditional formatting in Google Sheets which has to do with color scales.

Color scale conditional formatting is not possible yet. You can only use standard cell formatting features like fill color, border options, and number formats.

In any case, conditional formatting formulas are fantastic to use in Google Sheets.

Conclusion

There is a lot you can do with Google Sheets conditional formatting custom formula.

I hope that the examples in this guide will inspire you to create your own formulas that meet your specific needs.

Let me know in the below comments which example resonates with you.