A Guide to Using Conditional Formatting in Google Sheets

Depending on a specific condition, Google Sheets conditional formatting appears when you want to highlight data in a cell or row. It offers the possibility of making your work easier, especially when dealing with 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 takes the hassle out of manual filtering and helps you better understand the data presented to you.

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 met and will be ignored otherwise.

In this article, we’ll show you how to use conditional formatting in Google Sheets to make your job easier. Let’s get started.

1. How to use conditional formatting for text

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

2nd step: Next, click the format rules drop-down list and select a text option. For our sample data, we want to highlight all cells with items as pencil.

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

2. How to use conditional formatting for numbers

Step 1: Select a data range and go to Format> Conditional Formatting. You can also change the data range by clicking the table icon.

2nd step: Then select Format Rules and scroll down until you see the options for numbers. Select one of them and enter the value.

Then choose a formatting style and hit the Done button.

For our example sheet, we wanted to highlight cells with values ​​greater than 50. We selected the condition “Greater than” and added 50 in the text box. And that’s all. Any cells that have a value greater than 50 will be clearly highlighted.

To note: When it comes to digits and digits, setting the range is key to getting accurate results. For example, if you select the whole table and then choose a vague formatting rule, it would mess with the results.

Therefore, it is recommended that you select the column (or row) wisely before proceeding.

3. How to use conditional formatting on multiple columns

Google Sheets also lets you apply the same conditional formatting across multiple columns.

Step 1: Go to the formatting rules and click on the small table icon as shown below.

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

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

2nd step: Once done, select a format rule from the drop-down list with the style. You will see the highlighted data in no time.

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

4. Find all duplicate entries

Unfortunately, finding duplicates in a column cannot be handled with native rules. Here you will need to insert a custom rule. Fortunately, it is not rocket science.

Step 1: Once you have selected the column, scroll through the format rules and select “Custom formula is.”

2nd step: Add the following formula to the text box.

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

Here we want to highlight the duplicates in column E. Basically we want the sheet to look at the data from E2 to E7 and return a result if the number is greater than 1. Naturally you will need to replace the numbers from cell as per your data.

Step 3: Add the necessary formatting style and the cells will be highlighted instantly.

Edit the formula as follows if you want to highlight the entire row. Remember to change the range for the whole table (except the header row).

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

Nice tip: To highlight single cells, just replace ‘>’ with ‘=’.

5. How to highlight an entire row

So far, all of the above steps highlight specific cells. But for a very large dataset, it can get a bit overwhelming. Like the one above, here too you will have to resort to a tailor-made formula.

Step 1: Select the table range (except the table header) and go to the conditional formatting table. Now add the following formula under “The custom formula is”,

=$E2< 30

Here we want the leaves to only check column E for the expression. The $ reference is added to highlight the row. For text cells, although the same formula works, it only returns exact matches.

=$B2 = "[text]"

Likewise, if you want to highlight the line that does not have the said word, replace ‘=’ with ”.

 =$B2 <> "[text]"

6. How to highlight a row for multiple conditions

Step 1: Select the data range. Again, make sure you don’t include the header.

2nd step: Add the following formulas under Custom,

=or($B2="east", $B2="west")

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

Format your path

Understanding conditional formatting in Google Sheets isn’t very difficult. The good news is that some of the formatting can be taken care of by native rules. While the custom rules seem a bit overwhelming at first, it goes without saying that changing the expressions makes them easy to understand.