How to Use Conditional Formatting in Google Sheets?

Conditional formatting is a useful function that allows users to highlight certain cells if they meet specific criteria. There are many ways you can use conditional formatting for your needs, in a variety of situations. You can use the conditional formatting feature in Google Sheets to highlight specific values, format specific cells, and even identify duplicate content. This can make Google Sheets more user-friendly and more intuitive to understand, even for team members who are not familiar with Google Sheets.

One of the very many examples of using conditional formatting is if I have a dataset containing my sales member’s performance data, including their number of clients and total sales. Let’s say I want to highlight the names of all your sales team who have hit their monthly targets. You can do this quite easily, even dynamically as data is updated. Conditional formatting will change the color of cells when certain criteria are met, meaning that you can track progress in a variety of metrics at a glance.

In this article, you’ll learn how conditional formatting works in Google Sheets, knowing exactly how and when to use it. You’ll learn the basics of conditional formatting, how to use custom formulae to create specific conditions for your data, and also how to perform specific actions such as conditional formatting using multiple conditions, conditional formatting based on another cell, and, finally, conditional formatting to find duplicates.

What is conditional formatting?

Conditional formatting allows you to set rules associated with a particular cell(s). When these rules are met or fulfilled, you can specify a change to the visual aspect of the cell itself. These changes usually involve changing the color of a cell, but you can also change the text style within a cell. The conditional formatting feature in Google Sheets is made up of various drop-down menus where you can select your rules and apply it to your data easily.

You can set rules for each cell (or group of cells) using an “if/then” statement made up of the following elements:

• Apply to range: This box defines which cells the rule in question will apply to. For example, ‘A2:F9’ would apply the rule to all cells between A2 and F9.
• Format rules: Here you define the condition by which the rule is applied (and the associated effect). For example, “Format cells if” “Is not empty” would apply the rule whenever data is present in the cells in question. Any empty cells would stand out at a glance among any colored cells containing data.
• Formatting style: What do you want to happen when conditions are fulfilled? Here you can specify that when conditions are fulfilled, for example, the background of the cells in question turn cyan.

For example, let’s say I want to fulfill the following if/then statement: “If cell A1 is not empty then change the cell’s background color to green”. I can use the dropdown menus provided within the conditional formatting feature to create this statement, as you can see in the screenshot below.

How to use conditional formatting in Google Sheets?

There are many different ways you can use conditional formatting in Google Sheets, depending on your type of data or your desired action.

Let’s take the example mentioned above regarding a sales performance dataset. Using conditional formatting, I can highlight key data values, such as the highest or lowest sales or number of clients, highlight total sales that are within a certain range, or even find duplicate content. First, let’s go through a step-by-step for the most basic way to use the conditional formatting feature in Google Sheets.

There are two options when using conditional formatting in Google Sheets: “Single color”, and “Color scale”. Let’s explore how to use both.

How to use conditional formatting in Google Sheets: Single color

In this example, I want to highlight all of the total sales below $5,000 in red.

1. 1. Open up your Google Sheets file containing your data. Select the cells you want your conditional formatting rule to apply to (eg. ‘D1:D45’) and go to Format > Conditional formatting.

1. 2. The menu should appear on the right-hand side of your screen. Make sure you are on the “Single color” tab. As you can see, the cell range has already been set.

1. 3. Determine the formatting rule. From the dropdown menu under “Format cells if”, choose the condition that you need to trigger the rule. For example, if I want to highlight all of the total sales data below $5,000, I will select the “Less than” option. In the “Value or formula” field below, I will input ‘5,000’.

1. 4. Under “Formatting style”, specify the formatting for the cells that fulfill your rule. In this example, let’s set the cell background color to red. Once finished, click “Done”

1. 5. Your conditional formatting rules will be applied to your data. As you can see, all of the total sales below $5,000 are now highlighted in red.

You can also use the same steps to create formatting conditions greater than cells. Simply change the “Format cells if” drop-down to “Greater than” and add your value.

How to use conditional formatting in Google Sheets: Color scale

Now let’s say I want to apply a color scale to the ‘Total sales’ column. With this option, each cell background will become a different color or shade depending on its value. In this example, I want the lower total sales in red and the higher total sales in green.

1. 1. Just like the previous section, highlight your cell range and open the conditional formatting feature. This time, make sure you have selected the “Color scale” tab.

1. 2. Under the “Preview” section, click on the color scale and choose the palette your prefer. In order to fulfill my objective, I’ll choose the red-to-green scale.

1. 3. Next, choose the minpoint, midpoint, and maxpoint values and colors. Choose from the dropdown menus of each section the value you wish to base the colors from. To alter the colors, click on the paint can icons and select your preferred color. Once finished, click “Done”

1. 4. Your conditional formatting rules will be applied to your data. As you can see, the lowest total sales values are in the red color and the highest values in green.

How to use conditional formatting using a custom formula?

Using a custom formula as part of your conditional formatting is an extremely useful thing indeed. Not only does it give you the opportunity to create more personalized rules that can provide the exact outcome you want, but they can also be used to achieve other important objectives, as you will see further on in this blog post. You can use different functions such as the IF formula for conditional formatting that may help you solve more complex objectives. First, let’s explore how to use the custom formula to create formatting rules.

In this example, I want to highlight all the total sales that are between $4,000-$8,000 in orange. I can add a simple AND formula to achieve this quickly and easily.

1. 1. Highlight the data range you want to format and head to Format > Conditional formatting.
2. 2. In the “Format rules” drop-down menu, select “Custom formula”. In the field below, enter the formula that you need to fulfill your goal. In this case, to highlight the total sales between $4,000-$8,000, I need to add the following AND formula:

=AND(D1>4000,D1<8000)

1. 3. Under “Formatting style”, specify the formatting for the cells that fulfill your rule. In this example, I will set it to orange. Once finished, click “Done”.

As you can see below, all of the total sales between $4,000 and $8,000 have an orange cell background.

How to use conditional formatting based on another cell?

You can also change the color of a cell based on the value of another cell. This may be helpful when dealing with data that rely on each other a lot, or where you have a lot of dynamic data. This is one of the circumstances in which you need to use a custom formula. In this example, let’s say I want to highlight the names of the sales team who have less than 10 clients in red. In this example, the formatting of the sales team cells relies on the value of the number of clients.

1. 1. Highlight the range of cells you wish to format, then head to File > Conditional formatting.
2. 2. Under “Format rules”, select “Custom formula” from the drop-down menu. Add the formula that will create the correct condition, remembering to reference the other cell. In this example, my formula will be =C1<10. As you can see, I’m referencing the “Number of clients” column in the formula.

1. 3. Change your “Formatting style” preferences and click “Done”.
2. 4. As you can see, the names of my sales team who have less than 10 clients are now highlighted in red.

How to use conditional formatting in Google Sheets with multiple conditions?

There may be instances where you want to apply more than one condition to your data. Google Sheets’s conditional formatting feature allows you to create and apply multiple conditions to your data very easily. In this example, let’s say I want to highlight two things; the sales member with the highest total sales, and the one with the lowest total sales. This allows you to compare two columns of data at the same time. For this, we also need to use custom formulae for each rule. First, we need to create the first condition for the maximum sales total. Then we need to create another condition for the minimum sales total.

1. 1. Highlight the cell range you want to format and head to File > Conditional formatting.
2. 2. Create your first rule using the custom formula. In this example, let’s set up the maximum total sales condition using the custom formula =D:D=max(D:D) and changing the color to green. Click “Done”.

1. 3. Now click “+ Add another rule”.

1. 4. Create your second rule using the custom formula. In this example, let’s set up the minimum total sales condition using the custom formula =D:D=min(D:D) and changing the color to red. Click “Done”.

1. 5. As you can see, after applying multiple conditions, I now know that Melanie was top in total sales, and Ameline was the bottom.

Conditional formatting to find duplicates

Finally, conditional formatting is very useful for finding duplicate data in your Google Sheets. There are a few ways to do this, depending on where you are looking for duplicates within the data. All of these ways include using a custom formula.

Conditional formatting to find duplicates in a column

Let’s say I want to see whether any of my sales team members’ names have been repeated in my dataset. I can use the COUNTIF formula as my format rule to highlight any duplicates.

1. 1. Highlight the range of cells you wish to format (in this case, a single column). Click File > Conditional formatting.
2. 2. Under “Format rules”, select “Custom formula” from the dropdown menu and enter your COUNTIF formula. In this example, I’d use the formula ​​=COUNTIF($A$1:$A1,A1)>1.
3. 3. Apply your “Formatting style” preferences and click “Done”.

As you can see from the screenshot above, Charlie’s name has been highlighted as a duplicate.

Conditional formatting to find duplicates in multiple columns

To find duplicates across a range of columns, follow these steps:

1. 1. Highlight the range of cells you wish to format (in this case, multiple columns). Click File > Conditional formatting.
2. 2. Under “Format rules”, select “Custom formula” from the dropdown menu and enter your COUNTIF formula. In this example, I’d use the formula

​​=(COUNTIF($A$1:$A,A1)>1)*(COUNTIF($B$1:$B,B1)>1)

1. 3. Apply your “Formatting style” preferences and click “Done”.

As you can see from the screenshot above, Each duplicate cell has been highlighted.

Conclusion

Conditional formatting is a powerful and versatile tool that can help you to not only identify specific data but also help you avoid duplicate content. What’s more, conditional formatting in Google Sheets is dynamic – as data is updated, rules will be triggered, so that you always have the most accurate, up-to-date insights.

Now that you have come to the end of this article, you should be able to apply basic conditional formatting to your datasets, use custom formulas to create rules, and find duplicate data effectively. Take a look at our other blog posts for more Google Sheets-related how-to’s.