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.
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.
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.
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.
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.
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.
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.
Add the first range and then click on Add another range, to well, add the second range.
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’.
Step 2: Add the following formula to the text box.
=COUNTIF($E$2:$E$7,E2)>1
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
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")
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.
