Imagine being able to highlight specific cells in your Google Sheets based on certain conditions. With conditional formatting, you can easily achieve this and make your data more visually appealing and easier to analyze. In this article, we will provide you with a complete guide to conditional formatting in Google Sheets. You’ll discover everything you need to know on this topic, from basic color scales to advanced custom formulas.
Video Tutorial: Conditional Formatting in Google Sheets (Step-by-Step Guide)
Before we dive into the details, let’s start with a video tutorial that will walk you through the process of using conditional formatting in Sheets. This step-by-step guide will give you a visual demonstration of how to apply different formatting rules to your data.
Color Scale Conditional Formatting
Color scale conditional formatting is the simplest option to implement in Google Sheets. It allows you to assign different colors to your data based on its value. Here are a few examples of color scale conditional formatting:
To apply a color scale conditional formatting rule, select the range of data you want to apply the rule to. Then, click on “Format > Conditional formatting” in the top menu.
You will see the default conditional formatting rule: a single color (default green) applied to non-empty cells.
Next, click on the “Color scale” option in the top right corner of the menu. Now, you can adjust the minimum and maximum colors to create a color scale for your conditional formatting rule.
For example, in the above example, the lowest sale prices appear in dark red, while the highest sale prices appear in dark green. The intermediate prices appear in lighter shades of red and green. Click on “Done” and your table will look like this.
You also have the option to set minimum and maximum values for the conditional formatting. You can use fixed values, percentages, or percentiles.
Single Color Conditional Formatting
Single color conditional formatting evaluates each individual cell in the specified range of data. If a cell meets the formatting rule, the formatting style is applied to it.
To set up single color conditional formatting, select “Format > Conditional formatting” in the top menu. Then, choose the “Single color” tab.
The “Format cells if…” section is the most important aspect of single color conditional formatting. This section provides several unique formatting rules that we can use.
For example, let’s identify the dates in column A that start with “1”. To do this, select the range of data A2:A32 to select the dates. Then, set the rule “Starts with” with the value “1”. This will highlight all the dates in column A that start with “1”.
The available single color conditional formatting rules are:
- The cell is empty.
- The cell is not empty.
- The cell text contains a certain value.
- The cell text does not contain a certain value.
- The cell text starts with a certain value.
- The cell text ends with a certain value.
- The cell text exactly matches a certain value.
Date Rules
Date rules are another key set of rules for conditional formatting in Google Sheets. There are three types of date rules in Sheets. For date rules, we recommend using the DATE function to enter the dates.
For example, let’s apply conditional formatting to the date “11/01/2022”. Select the range of data A2:A32 and choose the rule “Date is” with “Exact date” under the “Format cells if…” section. Use the DATE function to enter the specified date (11/01/2022) in the input box.
The other available date rules are:
- Date is exactly the same as the specified date.
- Date is before the specified date.
- Date is after the specified date.
Number Rules
Number rules allow you to apply conditional formatting based on mathematical comparisons and ranges, such as greater than, less than, or between two values.
For example, if we want to format the dates that fall between two specific dates, say from January 15th to January 26th, we can use the “Is between” rule in number rules.
Open the conditional formatting rules, choose the dates column as the range of data (A2:A32). Then, set the following parameters:
- “Is between”
- “=DATE(2022, 1, 15)”
- “and”
- “=DATE(2022, 1, 26)”
Now, the dates within this range will be highlighted. Notice that the date range is inclusive (it includes both the minimum and maximum date).
The other available number rules are:
- Greater than
- Greater than or equal to
- Less than
- Less than or equal to
- Equal to
- Not equal to
- Is between
- Is not between
Custom Formula
Conditional formatting becomes incredibly powerful when determined by a custom formula.
To set a custom formula, open the conditional formatting rules by clicking on “Format > Conditional formatting”. Under the “Format cells if…” section, select “Custom formula is”.
For example, if we want to highlight the rows where “Ross” has made a sale, we can use the following formula:
= $B2 = "Ross"
This formula triggers the conditional formatting only when the value in column B is equal to “Ross”. The rows associated with Ross will be highlighted in light green.
Now, if we want to assign a different color to each row based on the associated employee, we can create a custom conditional formatting rule for each employee.
For each rule, make sure to select a unique color palette in the “Formatting style” section. You can see the result below: each row associated with the employee is highlighted in a distinctive color.
We can also create more advanced custom conditional formatting rules. For example, let’s group the sales based on the week they were made.
First, to streamline the process, let’s add a “Week Number” column to our table using the WEEKNUM formula.
Next, add an auxiliary column (column H) with the following formula:
=IF($A2="", "", COUNTIFS($F$2:F2, $F2, $H$2:H2, "") + 1)
Now, let’s define the conditional formatting rule. Set “Custom formula is” as follows:
=MOD($H2, 2) = 0
This custom formula groups the sales by week using a white or light green color code. It greatly facilitates the understanding of sales by week in January 2022.
Use AI to Write Conditional Formatting Formulas
As you can see, there are many ways to use conditional formatting in Google Sheets. It can be overwhelming at times, but you don’t have to do it alone.
You can use GPT Copilot by Coefficient to automatically generate any spreadsheet formula, including your conditional formatting formulas. To use GPT Copilot, you need to install Coefficient, which takes less than a minute.
Get started for free by simply submitting your email address. Follow the instructions for installation. Once the installation is complete, go back to the “Extensions” menu in Google Sheets. Coefficient will be available as an add-on.
Now, launch the app. Coefficient will run in the sidebar of your Google Sheets. Select GPT Copilot in the Coefficient sidebar. Then, click on “Formula Builder”.
Enter a formula description in the text box. For example, “Write a conditional formatting formula to highlight duplicate rows if the values in columns A and C are the same on multiple rows in Sheet11.”
Then, click on “Generate”. The Formula Builder will automatically generate the formula based on your example.
All you need to do is open “Conditional formatting” in the “Format” menu, add a new rule, select “Custom formula”, and paste your conditional formatting formula.
Conclusion
Conditional formatting is a powerful feature that allows you to visually enhance your data in Google Sheets. With color scales, single colors, date rules, number rules, custom formulas, and even AI assistance, you can create eye-catching and informative spreadsheets. Start exploring the possibilities of conditional formatting in Google Sheets today and make your data stand out!
To learn more about Google Sheets and unlock its full potential, visit Crawlan.com.
