Google Sheets Conditional Formatting Custom Formula (7 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 Menu section 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.

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

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 Google Sheets.

Let’s take a look at some examples.

Examples of Conditional formatting Custom formulas in

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 helpful to you. If not, I’m sure they will inspire you to create your own custom conditional formatting formulas in Google Sheets.

All the formulas used in these examples can be accessed below.

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)

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 Menu section 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.

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

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:

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 understand it better and create your own Google Sheets custom formula conditional formatting.

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

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)

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 18^th August 2021, the conditional formatting highlights weekend dates as follows:

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

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)

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

Example 7: Google Sheets Custom Conditional formatting to Highlight the Maximum values or Minimum values in a range

This is a reader-inspired example to write a formula that highlights the highest value in a range.

Using the custom and the preset conditional formatting rules, you can easily highlight the maximum or minimum values in a given range.

In this example, we will examine the two ways to achieve this result.

Option 1: Using Greater than or equal to preset conditional formatting rule to highlight the highest value in a range.

As you can see in the illustration above, the storage column has all the highest values selected. This was done using the Greater than or equal to ‘rule.’

Obey the following instructions to learn how we did it:

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

In this example, we will select cell D4:D16, that’s the column that has the values who’s highest we want to highlight.

Step 2: Go to Format > Conditional formatting.

Step 3: Select “Greater than or equal to” under Format rules.

NOTE: Please use the “Less than or equal to” preset if you wish to highlight the minimum value in the selection.

Step 4: Enter this Conditional Formatting Formula: =MAX(D4:D16)

This formula uses the MAX function to return ‘True’ of all the cells that contain the maximum value in the range, hence triggering the conditional formatting rule of highlighting the highest value.

NOTE: Please replace the MAX function with the MIN function if you wish to highlight the minimum values in the selection.

Step 5: Set the formatting under Formatting Style

Step 6: Click Done

Option 2: Using Custom Formula Conditional Formatting to highlight the rows with the highest value in Google Sheet

Below is the formula used for this task:

=$D4=MAX($D$4:$D$16)

Besides using the preset rule, you can also use a custom conditional formatting formula to achieve this functionality. This is particularly useful if you want your conditional formatting formula to highlight entire rows based on the highest value in a particular column.

Obey the below instructions on how to select or highlight rows based on the highest value in a particular column.

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

NOTE: If you wish for the conditional formatting to apply to entire rows or columns, select to include all the rows and columns in the dataset.

Step 2: Go to Format > Conditional formatting.

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

Step 4: Enter this Conditional Formatting Formula: =$D4=MAX($D$4:$D$16)

NOTE: Please replace the MAX function with the MIN function if you wish to highlight the minimum values in the selection.

Here you will have to adjust the formula according to your data.

If you want to apply this to your data, simply change the $D4 part of the formula to reference the cell that begins the column containing the values you use for the condition. Then change the $D$4:$D$16 part to match the range of the entire column.

Remember to include the dollar signs in your cell reference for the column letter. This will lock the column, preventing it from referencing other columns.

Step 5: Under Formatting Style, Set the formatting

Step 6: Click Done

And there you have it, a step-by-step guide to selecting or highlighting the highest value in a range using both a preset rule and a custom conditional formatting formula in Google Sheets.

Troubleshooting Custom 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.

Other Google Sheets Resources You May Find Useful

• How to Create Data Entry Form in Google Sheets
• How to Make a Bell Curve in Google Sheets
• How to Create a Combo Chart in Google Sheets
• How to Create a Timeline Chart in Google Sheets
• How to Create Dynamic Chart Range in Google Sheets
• How to Create an Area Chart in Google Sheets
• How to Create Pivot Table in Google Sheets
• How To Create Drop-Down List In Google Sheets (With Examples)