Imagine having a spreadsheet filled with data, and your eyes start to blur from the overwhelming amount of information. Understanding and making sense of it all can be challenging. That’s where conditional formatting comes in. It’s a powerful tool that helps you quickly grasp key data points, whether you’re tracking your expenses or monitoring your team’s progress towards quarterly goals.
In this article, I will guide you through the basics of conditional formatting in Google Sheets. I’ll show you how to apply some of the most common formatting rules. To follow along, you can use our demo spreadsheet by making a copy of it and trying the features as we go.
But before we dive in, here’s a quick reminder of how to use conditional formatting. (Scroll down for a detailed explanation and practice with our demo spreadsheet.)
- Select the range of cells to which you want to apply the formatting rules.
- Go to Format > Conditional formatting.
- In the Conditional formatting rules window, click on the dropdown menu in the “Format cells if…” section.
- Select the condition that should trigger your rule.
- Under “Formatting style,” choose the desired formatting style.
- Click “Done”.
Want to see all the rules in your spreadsheet? Simply select the entire range of data (or the entire sheet) and open the Conditional formatting rules window using the first step above. This will show you a complete list of all existing rules. You can quickly remove rules by selecting the “Delete rule” icon, which looks like a trash can.
What is Conditional Formatting in Google Sheets?
In Google Sheets, conditional formatting allows you to dynamically change the style of text and background color of a cell based on custom rules you set. Each rule you create follows an “if this, then that” statement.
For example, the rule might tell Google Sheets, “If any cell in column E is equal to 0%, change the background color of that cell to light yellow.”
Before we move forward, let’s clarify the three key components that make up each conditional formatting rule:
- Range: The cell(s) to which the rule applies. In the example above, the range is “E:E” (meaning the entire column E).
- Condition: This is the “if” part of the “if this, then that” rule. It refers to the triggering event that must occur for the rule to execute. In the example above, the condition is “is equal to”.
- Formatting: This is the “then” part of the “if this, then that” rule. It refers to the formatting style that should be applied to a given cell if the conditions are met. In the example above, the style is “light yellow background color”.
How to Use Conditional Formatting in Google Sheets
Conditional formatting is an effective way to highlight valuable information. Here’s how you can create and modify conditional formatting rules in Google Sheets.
1. Select a Range
First, select the desired range. You have two options to do this.
-
Option 1: Select a range directly in the spreadsheet. It can be a single cell or multiple cells spread across different rows and columns. If you’re not dealing with a lot of data, this is the quickest way to go.
-
Option 2: Without selecting anything, click on Format > Conditional formatting. In the Conditional formatting rules window, enter the desired range in “Apply to range”.
To add more ranges, click on “Select a range of data” (represented by a grid) from the cell range text box. In the “Select a range of data” window that appears, click on “Add another range”.
2. Create the Condition
Once you’ve selected your range, create the trigger rule (i.e., your “if this, then that” statement) in the “Format cells if” section. There are many triggers to choose from, which will impact how you format your rule. Let’s look at the most common triggers.
a. Apply Conditional Formatting with “Is empty” / “Is not empty”
The first set of triggers, “Is empty” and “Is not empty”, run based on whether there is or isn’t any data in the specified range.
Try this:
- In the demo spreadsheet, select cell A1 and click on Format > Conditional formatting.
- Select the “Is empty” trigger. Since the selected cell (A1) is empty, the default formatting will be applied. Magic! (Note: It’s not actually magic.)
b. Apply Conditional Formatting based on Text
To automatically format a cell based on its text, use one of these text-based triggers:
- Text contains
- Text does not contain
- Text starts with
- Text ends with
- Text is exactly
Try this: Let’s say you want to highlight all your employees based in the Tampa office.
- Select column B and click on Format > Conditional formatting.
- Under “Format cells if,” select “Text contains”.
- In the “Value or formula” field, enter “Tampa” (text values are not case-sensitive).
Now, the default formatting style will be applied to any cell containing the word “Tampa”. And since you applied the rule to the entire column B, whenever you add a new representative in the Tampa office, the cell containing “Tampa” will automatically be highlighted for easy access.
c. Apply Conditional Formatting based on Numeric Values
To automatically format a cell based on numeric values, use one of these triggers:
- Greater than
- Greater than or equal to
- Less than
- Less than or equal to
- Is equal to
- Is not equal to
- Is between
- Is not between
Try this: Let’s say you want to highlight “stretch” goals, which are goals with a 20% or higher increase.
- Select column E and click on Format > Conditional formatting.
- Under “Format cells if,” select “Greater than or equal to”.
- In the “Value or formula” field, enter “20%”.
d. Apply Conditional Formatting to an Entire Row
Applying conditional formatting to entire rows is slightly more advanced and requires the use of a custom formula.
Try this: Let’s say you want to quickly access all the data associated with your current Tampa representatives, including their names and sales goals.
- Select all your data (in this case, A3:F14) and click on Format > Conditional formatting.
- Under “Format cells if,” select “Custom formula is” (last option).
- In the “Value or formula” field, enter =$B:$B=”Tampa”.
Now, every row containing “Tampa” in column B will be highlighted.
But how does it work? Let’s break down the custom formula.
The = symbol indicates the start of the formula. $B:$B tells Google Sheets to look for a specific value in column B. By adding the $ before B, it tells Google to only look in column B. And finally, =”Tampa” tells Google Sheets what value to look for.
Now, let’s say you want to highlight all rows where the “stretch” goal is greater than or equal to 20%. Again, you would use a custom formula. In this case, you would enter =$E:$E>=20%.
If you’re applying a custom formula based on numeric values, you have multiple operators to play with: less than or equal to (<=), less than (<), greater than (>), or equal to (=).
You can also use a custom formula to highlight all rows that do not include a specific value. For example, if you want to highlight your representatives who are not based in the Tampa office, you would use the custom formula =$B:$B<>”Tampa”.
e. Apply Conditional Formatting using a Color Scale
Color scale is another formatting style that allows you to easily visualize values on a spectrum.
Try this: Let’s say you want to see where your representatives’ “stretch” goals fall on a spectrum, with a 0% increase not being great and a 50% increase being an outstanding stretch goal. A great way to visualize this is by using a color scale, where the fill color varies in intensity based on the cell’s value. Here’s how you can do it.
- Select column E and click on Format > Conditional formatting.
- In the Conditional formatting rules window, click on “Color scale”. The default formatting will appear, highlighting lower “stretch” goal percentages with a highly saturated color (in this case, dark green) and higher percentages with a less saturated version of the same color (in this case, light green).
f. Apply Conditional Formatting based on Dates
Before applying conditional formatting rules using dates, it’s important to use a consistent date format throughout the spreadsheet.
To do this, select the cells containing dates, click on Format > Number. From there, you can either select the default date format or choose “More date and time formats” and select the format you prefer.
Now that your date format is consistent, you can apply conditional formatting rules based on dates. Here are your options:
- Date is
- Date is before
- Date is after
Try this: Let’s say you want to highlight representatives who have a sales increase goal by the end of August.
- Select column F and click on Format > Conditional formatting.
- Under “Format cells if,” select “Date is before > Exact date”.
- In the “Value or number” field, enter “09/01/2023”.
g. Apply Multiple Conditional Formatting Rules
Adding more than one conditional formatting rule to a given range is easy. Simply select the range, click on Format > Conditional formatting, and click on “Add another rule”.
Google Sheets will execute each rule in the order they were created until it finds a condition that requires a style change. However, once a rule is filled by a given cell, subsequent rules will not override it.
In the example below, the following conditional formatting rules have been applied to the entire dataset (A3:F14):
- Rule 1: If the representative is based in Tampa, highlight the entire row with light green.
- Rule 2: If any representative’s sales increase is equal to 0%, highlight the cell with light yellow.
Jackson (row 7) works in the Tampa office. Because he fulfills the first rule, row 7 is highlighted in green. Even though Jackson also has a 0% increase in his sales goals and the second rule theoretically applies, it doesn’t matter to Google Sheets. It’s conditional formatting for… all the rules.
3. Select a Formatting Style
So far, we’ve mainly used Google Sheets’ default formatting style to highlight specific cells. But if you want to add a little more pizzazz, you can easily update your formatting style. Here’s how.
In the Conditional formatting rules window, click on “Default” under “Formatting style” to choose from five other predefined styles. If none of these options suit your needs, you can also create a custom style using the tools under “Formatting style”.
Bonus: How to Copy and Paste Conditional Formatting to Another Google Sheet
Let’s say you have multiple data sheets and you want to apply the same conditional formatting rules from one sheet to another in Google Sheets. You could apply the same rules manually, or you can take a much faster shortcut: copy and paste, but with a twist. Here’s what I mean.
In the example below, there are two sales data spreadsheets: one for 2022 goals and another for 2023 goals. In the “2022 Goals” sheet, the rule is “if the value is equal to 0%, highlight the cell in light green”. Here’s how to apply the same rule to the “2023 Goals” sheet.
- Click on any cell to which the desired rule is applied in the “2022 Goals” sheet and use your keyboard shortcut to copy it.
- Click on the tab of the “2023 Goals” spreadsheet.
- Highlight the data range in column E (E3:E14).
- Right-click and select “Paste values only > Format only”.
Any cell in column E with a value of 0% will immediately be filled with light green.
And there you have it! With these steps, you can now use conditional formatting in Google Sheets to easily visualize and highlight key information in your data. Whether you’re tracking expenses, monitoring sales goals, or analyzing any other type of data, conditional formatting is a powerful tool that helps you make sense of your spreadsheet.
For more tips and tricks on how to maximize your productivity with Google Sheets, check out Crawlan.com.
