Google Sheets conditional formatting appears when you want to highlight the data of a cell or row. It offers an opportunity to ease your work, especially when dealing with huge amounts 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 eliminates the hassle of manual sifting and gives you a better understanding of the data up front.
In layman’s language, conditional formatting is like a simple If-Else statement. A cell will be highlighted in a particular style if a criterion is met and will be ignored if not.
In this post, we’ll show you how to use conditional formatting in Google Sheets to make your work easier. Start.
1. How to use conditional formatting for text
Step 1: Select a specific column and click Format > Conditional Formatting. Here the selected range will be highlighted under Apply to range.
Step 2: Next, click the drop-down list for Formatting Rules and select a text option. For our sample data, we want to mark all the cells with the items as Pencil.
Step 3: Select formatting options from the menu under Formatting Rules. Once done, click Done. Cells that meet the selected criteria will be highlighted with the selected color.
2. How to use conditional formatting for numbers
Step 1: Select a range of data and navigate to Format > Conditional Formatting. You can also adjust the data range by clicking the table icon.
Step 2: Next, select Format Rule and scroll down until you see options for numbers. Select one of them and enter the value.
Next, choose a formatting style and press the Done button.
For our sample sheet, we want to highlight cells with values greater than 50. We selected the ‘Greater’ condition and added 50 to the text box. And that’s it. All cells with a value greater than 50 will be clearly marked.
Note: When it comes to digits and digits, determining the range is very important to get accurate results. For example, if you select the entire table and then choose an ambiguous formatting rule, it will confuse the results.
Therefore, you should choose columns (or rows) wisely before proceeding.
3. How to use conditional formatting on multiple columns
Google Sheets also allows you to apply the same conditional formatting across multiple columns.
Step 1: Navigate to Formatting Rules and click on the small table icon as shown below.
Add the first range and then click Add another range, well, let’s add a second range.
For our sample data, we want to highlight the contents of both columns E and F. Therefore, we have chosen the ranges E2 to E7 and F2 to F7.
Step 2: Once done, select the formatting rule from the drop-down list along with the style. You will see the highlighted data immediately.
Obviously the formatting rule will apply to both data sets.
4. Find all duplicate entries
Unfortunately, finding duplicates in a column cannot be handled using the original 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 to Formatting Rules and select ‘Custom formula as’.
Step 2: Add the following formula in the text box.
=COUNTIF($E$2:$E$7,E2)>1 
Step 3: Add the necessary formatting style and you will have the cells highlighted in no time.
Tweak the following formula if you want to highlight the entire row. Remember to change the range for the entire table (except the header row).
=COUNTIF($E$2:$E$7,$E1)>1
Cool Tip: To highlight unique cells, just replace ‘>’ with ‘=’.
5. How to Mark Entire Rows
So far, all the steps above highlight specific cells. But for a really large data set, it can be a bit overwhelming. Like above, here too you will have to use a custom formula.
Step 1: Select the table range (except for the table header) and go to the Conditional Formatting panel. Now add the following formula in the ‘Custom formula is’ section,
=$E2< 30
Here, we want the sheets to check only column E for the expression. For text-based cells, although the same formula works, it only returns an exact match.
=$B2 = “[text]”
Similarly, if you want to highlight the row without 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 a header.
Step 2: Add the following formulas to the Custom section,
=or($B2=”east”, $B2=”west”) 
With this formula, both East and West will be searched in column B. The $ expression highlights the entire row when the expression returns the actual value.
Format it your way
Understanding conditional formatting in Google Sheets isn’t too difficult. The good news is that some formats can be handled by native rules. While custom rules may seem a bit overwhelming at first, it goes without saying that tweaking the expressions makes them easy to understand.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
