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!
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”.
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.
From the file menu select Format followed by Conditional formatting.
From the Format rules section expand the “Format cells if” drop down menu. Scroll to the bottom and click “Custom formula is”.
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.
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
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.
A variety of custom formulas can be applied to conditional formatting from basic to more advanced.
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.
