Conditional formatting in Google Sheets is a feature that allows you to apply specific formatting to cells that meet certain criteria.
This feature is most commonly used as color-based formatting in Google Sheets to highlight, emphasize, or differentiate between data and information.
In this guide, we’ll walk you through detailed steps on how to use conditional formatting in Google sheets (with a lot of examples). We believe that examples are the best way to learn.
The animations below show one of the examples we’ll cover in this guide.
Let’s dive right in.
Google Sheets Conditional Formatting (Introduction)
Conditional formatting in Google Sheets, as the name implies, is based on conditions.
You set a condition that, if true, prompts Google Sheets to apply additional formatting to a cell. This new formatting can change the text color, underline, make text italic, bolden, strikethrough, and even change the cell’s background color.
This feature in Google Sheets allows you to draw attention to the most important information, making it easy to find.
For example, if you look at a worksheet that shows a company’s sales for the previous year, you want to be able to find underperforming products without having to sift through hundreds of cells.
Even if you’re not using Google Sheets for business, you’ll need to be able to zoom in on specific details in your personal spreadsheets, whether it’s a budget-busting dinner in your monthly expense worksheet or a missed week at the gym in your exercise log.
All too often, these crucial details are buried beneath a flood of data.
You can use the standard formatting tricks to make important data stand out from the crowd. The issue with this approach is that it is up to you to locate the cells that need to be formatted.
Not only is this approach a time-consuming task, but it also gets complicated when formulas are used. In Google sheets, formulas allow you to create complex calculations that link cells together, allowing a change to a single cell to cascade through your worksheet, changing data everywhere else.
If you are manually highlighting important information, you may need to repeat the entire formatting process whenever a value changes.
Fortunately, Google Sheets has a feature designed to save you time. It’s called conditional formatting, and it allows you to find and highlight important information automatically.
In this guide, you’ll learn how to use conditional formatting to highlight important data in your Google sheets.
Highlighting Specific Values using Conditional Formatting
To see how Google Sheets can highlight important values with conditional formatting, consider Mr. GSheets daily calorie sheet shown below.
I’ll consider this as our first learning example. After using this to explain some basic concepts, I’d add more examples to help you understand more complex stuff including conditional formatting formulas.
See the calorie sheet:
For several weeks, this worksheet records Mr. GSheets’ caloric intake. Mr. GSheets has noticed a significant weight gain during the same time period but is unsure when the overheating occurred. Fortunately, Google Sheets can highlight the problem areas with conditional formatting.
To use conditional formatting in Google Sheets, select the cells to be examined and formatted. Then, select the appropriate conditional rule. A rule is a set of instructions that tells Google Sheets when to use conditional formatting on a cell and when to ignore it. A typical rule might be, “If a cell value is less than 8,000, use bold formatting.”
Don’t worry if you don’t understand. I’ll soon explain each step of the process with pictures for you to get everything clearly.
If you simply want to get straight to the steps and complex examples, use the table of the content above.
That being said, let’s continue.
Google Sheets Conditional Formatting Rules
Google Sheets has a large number of conditional formatting rules that are divided into several categories (you can find them all on the Conditional formatting pane).
We have recategorized the formatting rules into the following two main categories.
- Rules for highlighting specific values. If your cell contains numbers or dates, you can specify a minimum, maximum, or range of values that Google Sheets should highlight. In the case of text, you can highlight cells that contain specific text, begin with specific text, and so on. When it comes to dates, you can select values that fall within specific ranges (last week, last month, next week, and so on). We’ll soon explore all of these details in this guide.
- Rules for highlighting values based on where they fall in a series. These options cause Google Sheets to highlight the highest values, lowest values, or values that fall above or below average.
To apply conditional formatting rules in Google Sheets:
- Select the cell or cells you want to apply the conditional formatting to.
- In the Menus, Select Format > Conditional formatting to reveal the conditional formatting pane.
- Under Format rules, select the formatting rule you want to apply.
- Under Formatting style, set the formatting you want to apply.
- Click Done to save the rule.
The above steps will guide you to apply any conditional formatting in your spreadsheets.
Now let’s dive into the various examples that will actually teach you all the steps you need to become a master with Conditional formatting in Google Sheets.
Click here to open the example spreadsheet. You may need to sign in to be able to make a copy and edit.
Example 1: Highlighting Cells with High or Low Values
In this example, we’ll quickly identify the days when Mr. GSheets enjoyed his meals with his amazing appetite.
Step 1: Select the Cell or Cells to be formatted
This step is easy.
Using your mouse pointer, click and drag over all the cells you want the conditional formatting to affect.
In this example, we’ll select all the cells under the Calories Taken column.
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: Set the Conditional Formatting Rule
Once it opens up, you can do this in three simple clicks.
First, under Format rules, click on the drop-down.
Second, select Greater than from the list.
Third, you’ll be given a text box to enter your value, enter 10,000, or any value according to your data.
This type of conditional formatting rule usually compares a cell to a fixed number.
You can, however, set conditions that use values from other cells in your spreadsheet.
To use this method, type “=” and then enter the cell you want to compare.
For example, if I want to compare the value in cell C8, I simply enter =$C$8 into the text box.
Step 4: Set the Formatting
There are a lot of formatting styles you can apply to the cells when they meet the condition you set.
You can do that by using the font controls under the Formatting style.
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.
See the animation below on how to select presets.
To define your own format settings, do so using the font controls.
When it comes to conditional formatting, Google Sheets does not allow the application of specific formatting. You cannot, for example, conditionally change the font size, but you can conditionally change other font settings such as Bold, Italic, Underline, Strikethrough, and Font color.
Aside from these restrictions, the font controls are the same as those found in the full-fledged format menu.
Step 5: Click Done
Google Sheets evaluates the condition and adjusts the formatting as needed as soon as you click the done button.
When you open your spreadsheet or change the value in one of the conditional cells, Google Sheets evaluates the condition and adds or removes formatting as needed.
Now Mr. GSheets’ days of appetite are apparent in the screenshot below. The highlights displayed here are the result of the settings we did in the above steps.
Adding Multiple Conditional Formatting in Google Sheets
So far, only one conditional formatting rule has been used in the preceding example. There is, however, no limit to the number of rules that can be applied concurrently.
Google Sheets allows you to use multiple rules in the same spreadsheet.
Rules can be set to format different sections of data. This allows you to highlight different values or cells by using multiple layers of conditional formatting.
You can also create rules that overlap with one another.
However, there is always the possibility of a conflict if you use conditional formatting rules that overlap.
For instance, one conditional formatting rule may apply a green background fill color to a cell while another applies a red background fill color to the same cell. If both rules affect the same cell, only one of them can win.
Which one, though?
It all depends on the order in which Google Sheets applies the conditional formatting rules. Later-applied rules take priority over earlier-applied rules.
Google Sheets applies rules in the order they were created, but if this isn’t what you want, you can change it in the conditional formatting pane.
Example 2: Using Multiple Rules to Highlight Cells
Let’s look at another example to make the above simple explanation even clearer.
In this example, as shown in the screenshot below, we will use multiple conditional formatting to apply green background fill to values less than 10,000 and yellow background fill to values greater than 15,000.
Let’s get right into it.
Step 1: Select the Cell or Cells for the multiple formatting rules
This is a no-brainer.
Click and drag your mouse pointer over all the cells you want the conditional formatting to apply to.
In this case, we’ll select all of the cells in the Calories Taken column.
Step 2: Go to Format > Conditional formatting
Here’s another simple step.
Simply move your cursor to the Google Sheets Menus and select the Format option.
This action will reveal all of the Format menu items, including Conditional formatting. Simply click on it to open it in the right-hand pane.
Step 3: Set the First Conditional Formatting Rule
When it’s open, you can add the first rule with a few simple clicks.
First, under the Format rules, click on the drop-down.
Next, select Less than from the list.
You’ll then be given a text box to enter your value, enter 10,000, or any value according to your data.
Then set the formatting you want to apply to cells that fall within the first rule you just set.
Step 3: Set the Second Conditional Formatting Rule
After setting the first rule, you have two options to add the second rule.
You can add the second rule simply by clicking on the Add another rule link at the bottom of the pane.
Another way to add another conditional formatting rule is to click on the Done button and you’ll be given the option to add more rules.
When you click the Add another rule link, Google Sheets will open a new pane where you can enter your rules and formatting.
Add the rules by first clicking on the drop-down menu under the Format rules.
Next, select Greater than from the list.
You’ll then be given a text box to enter your value, enter 15,000, or any value according to your data.
Now add the formatting you want for the cells that this rule applies to.
Step 3: Click Done
And here you have it. A single spreadsheet with multiple rules applied to the same range of cells.
You can add as many rules as you want to the same cells.
Just keep in mind that applying too many rules to the same range of cells can result in a formatting conflict.
When this occurs, Google Sheets will apply the rules in the order in which they were created. That is, later rules will take precedence over earlier ones.
Color Scale Conditional Formatting in Google Sheets
Another interesting feature of Google Sheets conditional formatting is the color scale.
Color scales in Google Sheets let you format different cells with different colors based on the cells’ values.
Unlike single-color conditional formatting, which allows you to define your own colors, Google Sheets assigns a predefined color to the lowest value, another predefined color to the highest value, and a weighted blend of the two to all values in between.
For example, if 100 is green and 1,000 is yellow, the value 500 gets a shade of green.
Color scales are a data visualization technique that can be used to drive quick interpretations of the intensity of events and make course corrections accordingly.
Let’s take a look at our third example to see how you can use color scale conditional formatting in Google Sheets.
Example 3: Using Color Scale to create Heatmap in Google Sheets
The screenshot below shows an example of color scale conditional formatting in Google Sheets.
This example depicts monthly sales for three countries. And conditional formatting is applied to the figures.
It is a three-color scale formatting, with green for the highest point value, yellow for the midpoint value, and red for the lowest point value.
As seen below, it’s clear that India has lower sales volumes.
Now let’s get into the steps to actually make this kind of color scale conditional formatting in Google Sheets.
Step 1: Select the Cell or Cells for the multiple formatting rules
Step 2: Go to Format > Conditional formatting
Step 3: Select the Color Scale tab
Once the conditional formatting pane opens, you’ll see the color scale tab right after the Single color tab.
Step 4: Set the color scale
Color formatting controls are available under format rules to assist you in specifying colors for the lowest, highest, and midpoint values.
If you don’t want to do things by hand, click the Preview colors button to select a preset format.
As you make select your colors, you’ll see the changes happen in real-time in your data. This will let you know right away if the colors you’ve chosen don’t work well with the data.
Step 5: Click Done
To apply the settings, click the done button, and you’ll see your lovely heatmap created.
Using Custom Conditional Formatting formulas in Google Sheets
Google Sheets’ conditional formatting feature is versatile, but sometimes it’s just not quite versatile enough.
Fortunately, you can extend its versatility by writing conditional formatting formulas in your spreadsheet.
I’d use the rest of the examples to describe how to create conditional formatting formulas to do the following:
- Identify certain text entries.
- Format cells that are in odd-numbered rows (for Dynamic alternate row shading)
- Highlight an entire row based on a condition.
- Highlight Duplicates, empty cells, and error cells.
You might find some of these formulas useful. If not, they may serve as inspiration for you to develop your own conditional formatting formulas.
Example 4: Highlight a row/column based on a matching value
When working with spreadsheets, you’re bound to come across a situation in which you need to highlight an entire row or column of data if certain conditions are met.
The screenshot below shows a Google Sheets spreadsheet with a conditional formatting formula. If a month chosen in Cell C2 appears in the first column, the entire row for that month is highlighted.
You can also learn how to make a drop-down list in Google Sheets by clicking here. It’s a comprehensive guide that will help you improve your spreadsheet skills.
Follow the steps below to learn how 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.
Next, 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 mixed 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 5: Creating Alternate-row shading with conditional formatting
Your spreadsheets will be easier to read if you use alternate row shading.
What makes it even more powerful is that the shading is automatically updated when a row is deleted or added.
An example of alternate row shading is shown in the screenshot below:
Follow the steps below to use conditional formatting to create alternative row shading in Google Sheets.
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: =MOD(ROW(),2)=0 or =MOD(ROW(),2)=1
To enter this formula, first, under the Format rules, click on the drop-down.
Next, select “Custom formula is” from the list.
You’ll then be presented with a text box to enter your custom formula, enter =MOD(ROW(),2)=0 or =MOD(ROW(),2)=1 depending on which row you want to start the shading from.
Now set the formatting you want the shaded rows to use.
Step 4: Click Done
If you follow all of the steps correctly, all of the alternative rows in your spreadsheets should be shaded.
The formula in this example uses the ROW function (which returns the row number) and the MOD function (which returns the remainder of its first argument divided by its second argument). The MOD function returns 0 for cells in even-numbered rows, and the cells in that row are formatted.
If you want to create alternate column shading, use the COLUMN function rather than the ROW function.
Example 6: Highlighting Duplicates using Conditional formatting
When working with large amounts of data in Google Sheets, you may come across many duplicate values.
In this example, I’d like to show you how to use conditional formatting to quickly identify duplicate values in Google Sheets.
See it in action in the screenshot below.
The steps below will show you how to highlight duplicate values in Google Sheets using a conditional formatting formula.
Step 1: Select the range of cells where you want to identify the duplicates.
Step 2: Go to Format > Conditional formatting.
Step 3: Enter this Conditional Formatting Formula: =COUNTIF($B$3:$D$14,B3)>1
To enter the formula, first, under the Format rules, click on the drop-down.
Next, select “Custom formula is” from the list.
You’ll then be given a text box to enter your custom formula, enter =COUNTIF($B$3:$D$14,B3)>1
Now set the formatting you want for the duplicate values.
Step 4: Click Done
When you have completed all of the steps correctly, all of the duplicate cells in your spreadsheets should be highlighted.
Example 7: Highlighting Empty cells in Google Sheets using Conditional formatting
If you have a large amount of data in a Google sheet that contains some blank cells, you can use conditional formatting to highlight all of the blank cells.
This example will show you how to do just that, as shown in the screenshot below.
Let’s dive right into the steps.
Step 1: Select the range of cells where you want to identify the empty cells.
Step 2: Go to Format > Conditional formatting
Step 3: Enter this Conditional Formatting Formula: =ISBLANK(B3)
To enter the formula, first, under the Format rules, click on the drop-down.
Next, select “Custom formula is” from the list.
You’ll then be given a text box to enter your custom formula, enter =ISBLANK(B3)
Now set the formatting you want for the empty cells.
Step 4: Click Done
If you follow all of the steps correctly, all of the empty cells in your spreadsheets should be highlighted.
Example 8: Highlighting Errors with Conditional formatting
When you start working with spreadsheets that have a lot of formulas, you’ll notice a lot of errors in cells that have incorrect formulas in them.
You can easily spot error cells using conditional formatting, and this example will show you how.
The image below depicts a spreadsheet with all of the errors highlighted.
Follow the steps below to highlight error cells in Google Sheets.
Step 1: Select the range of cells where you want to identify the error cells.
Step 2: Go to Format > Conditional formatting
Step 3: Enter this Conditional Formatting Formula: =ISERROR(B3)
To enter the formula, first, under the Format rules, click on the drop-down.
Next, select “Custom formula is” from the list.
You’ll then be given a text box to enter your custom formula, enter =ISERROR(B3)
Now set the formatting you want for the error cells.
Step 4: Click Done
When you complete the steps correctly, all of your error cells should be highlighted in your spreadsheet.
Copying Cells with Conditional formatting
In Google Sheets, conditional formatting is stored with a cell just like standard formatting is stored with a cell.
As a result, when you copy a cell with conditional formatting, you copy the conditional formatting as well.
In addition, if you insert rows or columns within a range with conditional formatting, the new cells will have the same conditional formatting.
When pasting, you can also copy only the formatting by selecting Paste Special.
Removing all Conditional Formatting in Google Sheets
In Google Sheets, deleting the content of a cell does not remove the cell’s conditional formatting.
As a result, you must take the necessary steps in order to completely remove conditional formatting from your spreadsheet.
To remove conditional formatting from your Google Sheets, simply click the Formatting menu, a drop-down list will appear, select Clear Formatting at the bottom of the drop-down. This will remove all the conditional formatting as well as all other cell formattings.
However, if you don’t want to remove other cell formattings, simply delete the conditional formatting rule.
Conclusion
Google Sheets’ conditional formatting is a very useful feature.
Knowing how to use this feature effectively will help you become more efficient in spreadsheets.
In the comments, please let me know what you think about this guide and the examples.
Other Google Sheets Resources You May Find Useful
- How to Create Data Entry Form in Google Sheets
- How to Add a Target Line in Google Sheets
- How to Create Pie Chart in Google Sheets
- How to Make a Scatter Plot in Google Sheets
- How to Make a Pareto Chart in Google Sheets
- How to Make a Bell Curve in Google Sheets
- How to Create a Combo Chart in Google Sheets
- How to Make a Histogram in Google Sheets
- How to Create a Timeline Chart in Google Sheets
- How to Find Slope in Google Sheets? Using Formula & Chart
- How to Create Dynamic Chart Range in Google Sheets
- How to Create an Area Chart in Google Sheets
- Step Chart in Google Sheets – A Step-by-Step Tutorial
- How to Add a Trendline in Google Sheets Charts
- How to Create Pivot Table in Google Sheets
- How To Create Drop-Down List In Google Sheets (With Examples)