Ready to discuss conditional formatting in Google Sheets? Below, Iโm providing step-by-step instructions on the process, including color gradient (scaling), using a checkbox, highlighting information, and much more.
Iโve created the most straightforward conditional formatting Google Sheets guide so you can discover everything about this critical spreadsheet function.
Using Excel? Check out our Using Excel conditional formatting guide!
This Article Covers:
How To Add Conditional Formatting in Google Sheets
You may want to use conditional formatting in Google Sheets for many reasons.
Say that you have 50 test scores. Conditional formatting can be used to highlight scores that are lower than 35. You can use countless parameters to highlight cells based on their value.
To add conditional formatting in Google Sheets:
- Select the cell range.
- Go to โFormatโ > โConditional formattingโ in the drop-down menu. A toolbar will open to the right.
- Click โAdd a rule,โ then choose between single color and color scale.
- Create your formula.
- Choose a color or format in the โFormattingโ style.
- Click โDone.โ
Itโs really that simple! The more challenging part is how you want to build your formula.
How To Access Conditional Formatting in Google Sheets
You must locate the command before entering advanced conditional formatting in Google Sheets. You can do this by selecting โFormatโ > โConditional Formatting.โ
This will open the โConditional format rulesโย pane that can be used to set your rules. You should see two options for conditional formatting in Google Sheets:
- Single color
- Color scale
Using Color Scale Conditional Formatting
โSingle colorโ can highlight all a cell’s background color based on a specific value. For example, I might highlight all the cells with scores less than 35. It wouldnโt matter if the score is 34 or 10: Both cellsโ background color will be affected.
Using Color Scale Conditional Formatting
โColor scaleโ can present varying color gradations between the cell values. For example, a score of 34 might be a lighter shade of red, while 10 would be a darker shade of red.
Examples of Conditional Formatting in Google Sheets
Below, Iโve included a fewย formulas you could use for conditional formatting in Google Sheets.
1. Highlight Scores Less than 35
I want to highlight the cells with scores less than 35. The first column is reserved for the studentsโ numbers, and the second column shows their marks. We want to highlight trends showing students’ results with a score below 35.
First, we need to select the data:
- Select the cells (B2:B16).
- Go to Google Sheet’s top menu and select โFormatโ > โConditional Formatting.โ
- Select โSingle color.โ
- Ensure that โApply to rangeโย refers to the correct range.
- In the โFormat cells ifโย drop-down, select โLess than.โ
- A new field should appear below the drop-down. Enter 35 in it.
- Select the format style. You can choose from the default colors or choose your own. In this case, I selected red.
- Click โDone.โ
This will instantly highlight all the cells with a score less than 35 in red (as shown below). You can easily do the same using another color for those who scored over 65.
Related: How To Freeze Panes in Google Sheets
2. Using a Heat Map for Google Sheets Conditional Formatting
Conditional formatting in Google Sheets offers more interesting visual options. Hereโs how to create a heat map in Google Sheetsย using conditional formatting:
To create the heat map using the exam scores, I selected the data the same way in Example 1. The difference comes when we select โcolor scaleโ instead of โsingle color.โ
- Select the data set in which you want to create the heat map.
- Go to โFormatโ > โConditional formattingโ > โColor scale.โ
- Be sure that โApply to rangeโ refers to the correct range. You can change it if it doesnโt.
- In the โPreviewโ drop-down, select your preferred gradient.
- The color on the left of the gradient is applied to the lower-value numbers โย and those on the higher side are applied to the higher-value numbers.
- When selecting the gradient, you can see a live preview.
- Note: In this case, I am selecting the red-to-green gradient.
- Click โDoneโ to create a heat map with gradient shading.
3. Google Sheets Custom Formula Conditional Formatting to Highlight Duplicate Data Points
To highlight duplicate data points in Google Sheets, use the custom function feature in conditional formatting.
Suppose you have the following dataset:
To highlight all the duplicate data:
- Select the data set.
- Go to โFormatโ > โConditional formatting.โ
- In the โConditional format rules,โ select โSingle Color.โ
- Be sure that โApply to rangeโย refers to the correct range. If it doesnโt, you can change it here.
- In the โFormat cells ifโ drop-down list, select the โCustom formula isโย option.
- Enter the following formula:
=COUNTIF($A$2:$A$11,A1)>1
- Select the format and click the โOKโ button to highlight all the instances of duplication.
Note: This is dynamic.ย This means that if you change the dataset, it will automatically highlight the cells containing duplicate values.
4. Highlighting Alternate Rows
In Google Sheets, highlighting alternate rowsย is preferred for printed reports and data sets that people review quickly. Simply put, adding color to whole rows improves readability and provides a more professional look.
Highlight alternate cellsย using conditional formatting in Google Sheets:
- Select the dataset.
- Select โFormatโ > โConditional Formattingโ > โSingle color.โ
- Be sure youโve selected the correct range (or change it).
- In the โFormat cells ifโย drop-down, select the conditional formatting โCustom formula isโย option.
- Enter the following formula:
=MOD(ROW(),2)=1
- Select the format.
- Click โOKโ to highlight alternate rows in the selected range.
How Do I Auto Highlight Every Other Row in Google Sheets?
The easiest way to do this is by highlighting your entire spreadsheet and selectingย โFormatโ > โAlternating Colors.โ Next, choose the theme you want to apply to your spreadsheet.
5. Highlight Blank Cells
Using the custom formula option to highlight blank cells in the dataset is easy. This is especially useful when there are hundreds or thousands of records. Suppose you have the following dataset:
Related: How to Count Sheets Cells That Arenโt Blank
Here are the steps to highlight all the blank cells in this data set.
- Select the dataset.
- Select โFormatโ > โConditional Formattingโ > โSingle color.โ
- Make sure โApply to rangeโ refers to the correct range or change it.
- In the โFormat cells ifโย drop-down, select the โCustom formula isโย option.
- Enter the following formula in it:
=ISBLANK(A2)
- Select the format and click โOKโ to instantly highlight all the blank cells in the data set:
6. Highlighting Errors with Google Sheets Conditional Formatting
If youโve imported the data from Excel or Text files โ or have used a lot of formulas โ thereโs a possibility that some of the cells will have an error value. It would be best if you fixed these error values.
Suppose you have the following dataset:
To highlight all the cells that contain errors:
- Select the dataset.
- Select โFormatโ > โConditional Formattingโ > โSingle Color.โ
- Be sure that โApply to rangeโย refers to the correct range or change it.
- In the โFormat cells ifโย drop-down, select the โCustom formula isโย option.
- Enter the following formula:
=ISERROR(A2)
- Select the format and click โOKโ to highlight all the cells with any error (as shown below).
7. Highlighting Cells That Contain the Searched String
You can use conditional formatting in Google Sheets to create a searchable database.
When I enter a name in cell C2 and hit โEnter,โ it will highlight that name in the data set. If I enter a partial name, it will highlight all the cells that contain part of that string. For example, if you enter โPโ in cell C2 and hit the enter key, it will highlight the names of Pat and Paul.
- Select the dataset.
- Go to โFormatโ > โConditional Formattingโ > โSingle color.โ
- Make sure the โApply to rangeโ refers to the correct range โ if it doesnโt, change it here.
- In the โFormat cells ifโย drop-down, select the โCustom formula isโย option.
- Enter the following formula in it:
=AND(NOT(ISBLANK($C$2)),ISNUMBER(SEARCH($C$2,A2)))
- Select the format and click โOK.โ
From now on, whenever you enter a string in cell C2 and hit enter, it will automatically highlight all the cells that contain that string.
8. Conditional Formatting Google Sheets for an Entire Row
To conditionally format an entire row, you have to:
- Select the row header.
- Navigate to โFormatโ > โConditional formatting.โ
- Set your conditional formatting rules.
9. Conditional Formatting Using a Checkbox
Letโs say I want to include a grocery list in my spreadsheet. I can highlight the checked items by using conditional formatting.
- Select the dataset.
- Go to โFormatโ > โConditional Formattingโ > โSingle color.โ
- Make sure the โApply to rangeโ refers to the correct range โ if it doesnโt, change it here.
- In the โFormat cells ifโย drop-down, select the โCustom formula isโย option.
- Enter the following formula to highlight the cells with checked boxes:
=B2=TRUE
- This custom conditional formatting Google Sheets will highlight cells in column A with checked boxesย in column B.
10. Google Sheets Conditional Formatting with Multiple Rules
Having more than one conditional formatting formula in Google Sheets is possible. In my checkbox sheet, I can highlight the checkboxes in green and the unchecked boxes in red.
- Select a cell in your data range
- Go toย โFormatโ > โConditional formatting.โ
- In the โConditional Format rules,โ select โSingle color.โ
- In the โFormat cells ifโ dropdown menu, select the โCustom formula isโย option.
- To highlight the cells with checkboxes, enter the following formula:
=B2=TRUE
- Click โDoneโ > โAdd another rule.โ
- Under the โFormat cells ifโย drop-down, select the โCustom formula isโย option again.
- Enter the following formula to highlight the cells with unchecked boxes:
=B2=FALSE
- Go to formatting style and choose your preferred color.
- Click โDone.โ
Watch How To Apply Conditional Formatting Based on Another Cell Value in Google Sheets
No time to read through my whole article? No problem! Here is a quick shortcut to teach you how to apply conditional formatting based on another cell value. Enjoy!
How To Remove Conditional Formatting in Google Sheets
If you want to remove conditional formatting in your Google spreadsheet:
- Select a cell in your data range
- Go to โFormatโ > โConditional formatting.โ
- Click the โtrashโ icon beside the rule you want to remove.
- This will delete the conditional formatting applied to the whole row of your data range.
How To Remove Conditional Formatting from One Cell
Toย remove formatting from a selected cell, select the cell and go to โFormatโ > โClear formatting.โ
Itโs just that simple!
Google Sheets Conditional Formatting Rules
We use conditional format rules in Google Sheets to apply the correct conditions (e.g., highlight specific values). You can find them under the drop-down that says โFormat cells if:โ
Is Empty & Is Not Empty Formatting Rules
These two formatting rules add a conditional format to a range of cells. They depend on whether the cell has values or is empty.
โIs emptyโ will add the format to any empty cell within the selected range. โIs not emptyโ will do the opposite.
Text Rules
There are 5 text rules in Google Sheets. See below to understand how each rule is used to format cells:
- Text contains: Format any cell that has the mentioned text.
- Text does not contain: Format any cell that doesnโt contain the mentioned text.
- Text starts with: Format any cell that contains the mentioned text at the beginning.
- Text ends with: Format any cell that contains the text mentioned at the end.
- Text is exactly: Format any cell that only contains the mentioned text.
Date Rules
Date formatting rules format cells with specific dates as the condition. The three most popular date rules include:
- Date is: Format cells that contain a specific date mentioned.
- Date is before: Format cells that contain any date before the date mentioned.
- The date is after: Format cells containing any date after the date mentioned.
Number Comparison Rules
These formatting rules work specifically with numerical values and are often used as comparison operators to create conditions. Below are the right most common number comparison rules:
- Greater than: Format cells if the number is larger than the value mentioned.
- For example, if the formatting rule is โgreater than 10,โ the rule will highlight all the cells with a number larger than 10.
- Greater than or equal to: Format cells under the condition that the number is largerย or the exact value mentioned.
- Less than: Format cells if the number is smaller than the value mentioned.
- Less than or equal to: Format cells if the number is smaller than the value mentioned.
- Is equal to: Format cells under the condition that the number is the exact value mentioned.
- Is not equal to: Format cells if the number is other than the value mentioned.
- Is between: Format cells if the number is within the range mentioned.
- Is not between: Format cells if the number is not within the range mentioned.
Custom Formula
Custom formulas are typically used for advanced conditional formatting (that canโt be applied using the formatting rules weโve mentioned so far). That said, Iโve reviewed most of Google Sheets’s conditional formatting custom formula examples.
Frequently Asked Questions
What Is Google Sheets Conditional Formatting?
Conditional formatting in Google Sheetsย is an automatically applied formatting of cells based on requirements set by the user. Youโll apply conditional formatting rules to format your data based on those rules.
How Do I Use Conditional Formatting in Google Sheets?
You can use conditional formatting to make the data in your spreadsheets more scannable. The simplest way to apply conditional formatting is to:
- Highlight the cells you want to apply conditional formatting.
- Navigate to โFormatโ > โConditional formatting.โ
- Set your conditional formatting rules.
Can I Copy and Paste Conditional Formatting into a New Google Sheet?
Yes, you can copy and paste items formatted into a new Google Sheet. Follow these easy steps:
- Copy the cells you wish to paste.
- Right-click and chooseย โPaste Special.โ
- Select โPaste Format Only.โ
- Finally, paste your selection into the new Google Sheets location.
How Do You Conditionally Change Cell Color in Google Sheets?
You can change the cell fill color under the โFormatting styleโ heading of the Conditional formatting menu bar.
How Do I See Conditional Formatting Rules in Google Sheets?
To see all the conditional formatting rules at once, head over toย โFormatโ > โConditional formatting.โ Click on any existing rules to see the cells they apply to.
How Do I Change Conditional Formatting Colors and Rules?
- Navigate to โFormattingโ > โConditional formatting.โ
- Click on the existing rule youโd like to change.
- Change the rule as though you were making a new one.
- Change colors with the fill color option.
Finishing up
So there you have our guide to conditional formatting Google Sheets! If youโre looking for more tips and tricks on using spreadsheets like a boss, check out Udemyโs enormous range of Google Suite courses!
Related:
Is there a way to protect the conditional formatting so that viewers can’t see the criteria?
have a question regarding google sheets conditional formating.
I am trying to have cells A13:D13 change color based on data contained in cell D13.
cell D13 will has multiple conditional rules.
If D13 ends with the letter “c” i want A13-D13 to turn a color “purple”. I can get it to work by using this formula. =$D13=”c”
But when D13 contains other text such as 1w1, 2w2 c. The formula no longer changes the cells from A13-D13 purple.
If i use the google formula that says text ending in “c”. that will only change the cell D13, not the line from A13-D13.
I have been able to get it to work if I add a column and just put the letter “c” in column E and write it A13-E13 formula =$E13=”C” – changes to purple. I wanted to reduce the size of my spreadsheet and not have to create 8 additional columns to add the “C” in the column by itself.
Hope that makes sense. Hope someone has a solution. I have looked and looked and couldnt find one on line.
Can you explain how conditional formatting works with the output of QUERY? It appears mixed columns of dates and text get returned as text, so conditional date formats don’t apply correctly.
Phantastic