A Guide to Using Con­di­tion­al For­mat­ting in Google Sheets

Namrata Gogoi

Google Sheets' conditional formatting comes to the picture when you want to highlight a cell or row's data. It offers the opportunity to ease your job, especially when handling a massive amount of data. So, whether you want to highlight a particular set of numbers or a certain type of name, it can be done in a jiffy. Plus, it removes the hassle of manual screening and helps you understand the data in front of you in a better way.

A Guide to Using Conditional Formatting in Google Sheets

In layman's language, conditional formatting is like a simple If-Else statement. A cell will be highlighted in a specific style if a criterion is satisfied and will ignore if not.

In this post, we will show you how to use conditional formatting in Google Sheets to ease your job. Let's get started.

1. How to Use Conditional Formatting for Text

Step 1: Select a particular column and click on Format > Conditional formatting. Here, the selected range will be highlighted under Apply to the range.

Guide to Using Conditional Formatting in Google Sheets 1

Step 2: Next, click on the dropdown for Format rules and select one text option. For our sample data, we want to highlight all the cells with items as Pencil.

Guide to Using Conditional Formatting in Google Sheets 2

Step 3: Select the formatting options from the menu under Formatting rules. Once done, click on Done. The cells meeting the selected criteria will be highlighted in the selected color.

Guide to Using Conditional Formatting in Google Sheets 3

2. How to Use Conditional Formatting For Numbers

Step 1: Select a range of data and navigate over to Format > Conditional formatting. You can also tweak the range of the data by clicking on the table icon.

Step 2: Next, select Format rules and scroll down until you see the options for numbers. Select either one of them and enter the value.

2021 05 28 19 49 53

Next, pick a formatting style and hit the Done button.

For our sample sheet, we wanted to highlight the cells with values greater than 50. We selected the 'Greater than' condition and added 50 in the text box. And that's it. All the cells which have a value greater than 50 will be clearly highlighted.

Guide to Using Conditional Formatting in Google Sheets 4

Note: When it comes to digits and numerals, defining the range is vital to get precise results. For example, if you select the whole table and then pick a vague formatting rule, it would confuse results.

Hence, it's recommended to select the column (or row) wisely before proceeding.

3. How to Use Conditional Formatting Across Multiple Columns

Google Sheets also allows you to apply the same conditional formatting on multiple columns.

Step 1: Navigate over to Formatting rules and click on the little table icon as shown below.

Guide to Using Conditional Formatting in Google Sheets 5

Add the first range and then click on Add another range, to well, add the second range.

Guide to Using Conditional Formatting in Google Sheets 6

For our sample data, we want to highlight the contents of both the E and F column. Hence, we have selected the ranges E2 to E7 and F2 to F7.

Step 2: Once done, select a format rule from the dropdown list along with style. You will see the data highlighted in no time.

It goes without saying that the format rule should apply to both data sets.

4. Find All Duplicate Entries

Unfortunately, finding the duplicates in a column can't be handled with the native rules. Here, you will have to insert a custom rule. Thankfully, it's not rocket science.

Step 1: Once you have selected the column, scroll down on the Format rules and select 'Custom formula is'.

Guide to Using Conditional Formatting in Google Sheets 11

Step 2: Add the following formula to the text box.

=COUNTIF($E$2:$E$7,E2)>1
Guide to Using Conditional Formatting in Google Sheets 7

Here, we want to highlight the duplicates in column E. Basically, we want the sheet to examine the data starting from E2 through E7 and return a result if the count is greater than 1. Naturally, you will have to replace the cell numbers as per your data.

Step 3: Add the necessary formatting style, and you will have the cells highlighted instantly.

Tweak the formula to the following if you want to highlight the entire row. Remember to change the range to the whole table (except the header row).

=COUNTIF($E$2:$E$7,$E1)>1

Cool Tip: To highlight the unique cells, simply replace '>' with '='.

5. How to Highlight an Entire Row

So far, all the steps above highlight specific cells. But for a really big data set, it can get a bit overwhelming. Like the one above, here too, you'll have to resort to a custom formula.

Step 1: Select the table range (except the table header) and head over to the Conditional formatting table. Now, add the following formula under 'Custom formula is',

=$E2< 30
Guide to Using Conditional Formatting in Google Sheets new

Here, we want sheets to check only the E column for the expression. For text-based cells, though the same formula works, it only returns the exact matches.

=$B2 = "[text]"

Similarly, if you want to highlight the row that doesn't have the said word, replace '=' with '<>'.

 =$B2 <> "[text]"

6. How to Highlight a Row for Multiple Conditions

Step 1: Select the range of data. Again, make sure that you are not including the header.

Step 2: Add the following formulae under Custom,

=or($B2="east", $B2="west")
Guide to Using Conditional Formatting in Google Sheets 9

With this formula, the will search for both East and West in column B. The expression $ highlights the entire row when the expression returns a true value.

Format Your Way Through

Understanding conditional formatting in Google Sheets is not terribly hard. The good news is that some of the formattings can be taken care of by the native rules. While the custom rules do seem a bit overwhelming initially, it goes without saying that tweaking the expressions makes them easy to understand.


The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.

Read Next

Join the newsletter

Namrata loves writing about products and gadgets. She has been working for Guiding Tech since 2017 and has around three years of experience writing features, how-tos, buying guides, and explainers. Previously she worked as an IT Analyst at TCS, but she found her calling elsewhere.