Conditional formatting in Google Sheets helps you format cells, rows or columns, change the text or the background color provided they meet the conditions you specify. The conditions may be based on a text or a number.
Conditional formatting helps you identify a cell (or range of cells) value that is ‘interesting’. It helps emphasize unusual values in a given data value that are higher or lower than a given threshold, e.g., you can visualize such data by using data bars or color scales, making it easier to take note of.
In this guide, we will look at how to use conditional formatting in Google Sheets.
Let’s take an example.
I have collated fasting blood sugar readings for all of my immediate family members, and I plan to update this data every three months regularly. To flag potential pre-diabetic conditions, I need to identify instances when blood sugar readings go above 100 mg/dL.
If this was a one-off exercise, I could manually read through the readings and do it. But since I plan to do this regularly, I need a more robust way of flagging such potential instances.
This is where conditional formatting will help me. I can format all cells with a value of over 100, and this will easily catch my eye as more data points get inserted.
Let us now look at using such conditional formatting in Google Sheets with the help of examples.
A Real Example of Using Conditional Formatting
Take a look at the below data where I have captured fasting blood sugar readings for immediate family members over the last year and a half:
As you can see below, I have used conditional formatting to highlight the cells where the reading has gone above 100 mg/dl with a red background color:
You may try changing the cell color or threshold values and see how the result changes. Go ahead and make a copy of the spreadsheet using the link I have attached below:
Awesome! Let’s do a detailed walkthrough of using conditional formatting in Google Sheets.
How to Apply Conditional Formatting in Google Sheets
There are three basic things you need to keep in mind before applying conditional formatting in Google Sheets. The formatting follows an If-Then rule. Based on the rules you provide, Google Sheets will apply the specified formatting to all cells that qualify.
In short, in order to apply conditional formatting, you need to mention:
- range: refers to the cell(s) where you want to apply the formatting.
- criteria: is the criteria that will trigger the formatting specified in the style.
- style: is the change that will reflect in the cells once the criteria are satisfied.
Let’s go ahead and learn how to use conditional formatting in a practical setting with an example.
How to Use Conditional Formatting in Google Sheets
Below is the nutritional information for ten different types of biscuits that are available at retail stores in a given city. The objective is to capture this information for an exhaustive set of biscuits and make an informed choice about which one of these I can safely add to my diet.
Example 1: Conditional Formatting with Texts
- Let’s say I want to flag all the biscuits from the Tesco Brand. The column that I am interested in, therefore, is Column A: Brand. Now, to use conditional formatting rules on this column, make a selection of the data range, in this case, A2: A11.
- Once the data range has been selected, navigate to the Format tab and click on Conditional formatting:
You will notice that a tab named Conditional format rules opens on the right side of the sheet.
3. Here is where you specify the three things—the range, the criteria, and the style.
4. In the Apply to range option, ensure that the range corresponds to your desired selection of data.
- The Format cells if option lets you choose when to format the selection of cells based on the style you specify immediately below it. You can conditionally format text based on whether they are empty, they contain a certain string, or what they start or end with. For our use-case, go ahead and choose Text is exactly.
- After selecting the format rule, provide the value that the rule has to match with. In this example, we give the value ‘Tesco’, since we want to format cells where the biscuits are from the Tesco brand.
- Finally, the Formatting style option lets you format the cells that meet the criteria mentioned in step 6. For this example, I will give a green background color to the cells which meet the criteria. Click on Done. If you have done everything correctly up to this point, you will have an output as shown below:
Example 2: Conditional Formatting with Numbers
- Continuing with the same example, I will now show you a similar formatting exercise, this time based on numbers. Let’s say I want to format all biscuit variants that have a Sugar or Calorie share of less than 5%. These are the ones I am willing to include in my diet.
- In the same way as above, once I select the data range as I2:I11, I will navigate to the Conditional formatting tab.
- In the Format cells if option, choose Less Than and give a value of 0.05 (which corresponds to 5%). Click on Done. It would be best if you had an output that looks like the one below:
You may also use conditional formatting on dates or even create your own custom formula and use it in criteria. You have everything you need to get started with using conditional formatting on Google Sheets. I recommend experimenting with different criteria and using them along with the numerous Google Sheets formulas available, and seeing what you can come up with.