In this article:

Conditional Formatting with Custom Formulas in Google Sheets

May 8, 2024

In this article we will show you how to apply conditional formatting using custom formulas in google sheets. Simply follow our steps below and copy the example formulas and you can apply conditional formatting in just a few clicks! 

Use a Custom Formula to Apply Conditional Formatting

For the purpose of this demonstration an example data set has been created. Click here to access the sheet and follow along at home.

In this example our dataset contains data for 7 people including their age, state and city. We will apply a custom formula that will apply formatting based on the conditions of any age less than (<) 45 and the city is “Los Angeles”.

1. Select the cell or range of cells that you want to apply the conditional formatting to

Highlight the cell or the cell range you want to include in the conditional formatting. To select a cell range simply click and drag across the cells you want to highlight or to highlight separate cells press and hold the Ctl (Cmd ⌘ for mac) and click on the individual cells.

In our data set we will select all cells except the header row.

Google Sheets conditional formatting custom formula: Highlight cells‍

2. Click "Format" in the File Menu, then click "Conditional formatting" 

From the file menu select Format followed by Conditional formatting.

Custom formula conditional formatting Google Sheets: Access conditional formatting

3. In the "Format cells if" dropdown menu, select "Custom formula is."

From the Format rules section expand theFormat cells ifdrop down menu. Scroll to the bottom and click “Custom formula is”.

Google Sheets custom formula conditional formatting: Format rules

4. In the "Value or formula" field, enter the formula that you want to use for the conditional formatting.

Below the “Format cells if” dropdown menu an input box will now appear. Enter your custom formula.

In our example we are going to insert a custom formula to apply formatting to all the records  of any person with an age less than (<) 45 and the city is “Los Angeles”.

Our example formula:

=IF(AND($C4<45, $E4="Los Angeles"),True,False)

Formula Breakdown:

$C4<45: This is the first condition and checks Column C for any cells with the value less than(<) 45(Not including 45 exactly).  

$E4=“Los Angeles”: This is the second condition and checks Column E for any cells containing the text “Los Angeles

Our custom formula example is an IF statement that compares two conditions and assigns a True or False value if both conditions are met. Conditional formatting is only applied if the assigned value is True.

Custom conditional formatting Google Sheets: Insert custom formula

5. Choose the formatting options that you want to apply to cells that meet the conditions specified in the formula.

In the Formatting style section choose which options you would like to apply. The text can be made bold, italic, underlined or stricken through. You can also change the text or the cell background color. 

For our example we will bold the text and change the cell background color to light purple

Conditional formatting Google Sheets custom formula: Format style

6. Click the "Done" button

Click done and your conditional formatting will be applied using your custom formula.

In our example we can see that although there are multiple people that have an age under 45 only one record also lives in “Los Angeles” and therefore meets the conditions and formatting applied.

Conditional formatting custom formula Google Sheets: Results

Here's some examples of the custom formulas you may use for conditional formatting:

A variety of custom formulas can be applied to conditional formatting from basic to more advanced. 

Formula Example and Breakdown Description
=CellRef<50 =C4<50

C4: Cell reference value

<50: is less than 50
This formula will evaluate TRUE if the value in cell C4 is less than 50, and FALSE if it is not.

If the evaluation is TRUE, formatting will be applied.

If the evaluation is FALSE the formatting will not be applied.
=CellRef= "TextValue" =D2= "Smith"

D2: Cell reference value

="Smith": Checks the text value is exactly "Smith"
This formula will evaluate TRUE if the value in cell D2 contains the text “Smith” and FALSE if not.

If the evaluation is TRUE, formatting will be applied.

If the evaluation is FALSE the formatting will not be applied.
=IF(AND(Condition1, Condition2),True,False) =IF(AND($C4>10, $A4="Doe"),True,False) This formula will only evaluate TRUE if the value of any cells below C4 (including C4) contains a number above 10 AND any cells below A4 contain the text “Doe”.

It will return FALSE if ANY of the conditions are not met.

If the evaluation is TRUE, formatting will be applied.

If the evaluation is FALSE the formatting will not be applied.

If you enjoyed this article, you might also like our article on how to use conditional formatting with checkboxes in Google Sheets or our article on how to color every other row in Google Sheets.

If you want to learn how to use Google Sheets data validation, we also suggest checking out our detailed guide. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->