fbpx
Blog /

A Complete Guide to Conditional Formatting in Google Sheets

This is a complete guide to conditional formatting in Google Sheets. In this blog, you’ll learn everything you need to know about the topic.

In Google Sheets, we can apply a custom format to a cell based on its values or the values of different cells. This is called conditional formatting and it’s a potent tool to visually accentuate data and tables.

Google Sheets provides two types of conditional formatting: color scale and single color. While each operates similarly, there are key differences in how each option works.

Here’s a full overview of both types of conditional formatting in Google Sheets, based on real examples and step-by-step walkthroughs.

Color Scale Conditional Formatting

Despite the complicated sounding name, color scale conditional formatting is the simplest option to implement in Google Sheets. Let’s look at some examples of color scale, based off the following dataset:

conditional formatting dataset

On the top menu of Google Sheets, click Format > Conditional formatting. This will take us to Conditional format rules.

To create a conditional formatting rule, we must select the data range we want to apply the rule to. In this case, let’s choose the Sales Price (E2:E32).  

We’ll see the default conditional formatting rule: a single color (default green) applied to Is not empty (non-empty) cells.

Single color conditional formatting google sheets

Then click the Color scale option on the top right of the menu.

conditional formatting color scale google sheets

Now we’ll adjust the colors of the Minpoint and Maxpoint to create a color scale for our formatting rule.

In the example above, lower sales prices will appear dark red, while higher sales prices will appear dark green.

Mid-range prices will appear in lighter shades of red and green. Click Done and our table will look like this:

color scale conditional formatting google spreadsheet

We can change the Minpoint, Midpoint, and Maxpoint to flat numbers, flat percents, or percentiles. We can also set Min value and Max value properties for the conditional formatting.  

Single Color Conditional Formatting

Single color conditional formatting evaluates each individual cell in the specified data range. If a cell passes the formatting rule, then the Formatting style is applied to it.

Formatting style conditional formatting google sheets

To set up single color conditional formatting, select Format > Conditional formatting. Then choose the Single color tab.

The Format cells if… section is the most important aspect of single color conditional formatting. This section offers several unique formatting rules we can deploy. Let’s go through each rule below.

Text Rules

Although Text rules suggest they can only be applied to text-formatted cells, we can actually use them on any kind of cell.

For example, let’s identify the dates in column A with days that begin in “1”.

First, choose the data range A2:A32 to select the dates. Then set the Text starts with rule to “1”. This will highlight any dates in column A that start with “1”.  

The result: cells A11:A20 are highlighted in light green, as specified by the Formatting style.

text starts with conditional formatting

Here’s a complete list of all the text rules available in Google Sheets.

Conditional formatting is applied if…

  • Is empty – the cell is empty or contains only spaces
  • Is not empty – the cell contains any character other than spaces
  • Text contains – the cell value includes the defined input
    text contains conditional formatting google sheets
  • Text does not contain – the cell value does not include the defined input
    text does not contain google sheets conditional formatting
  • Text starts with – the cell value starts with the defined input
    text starts with conditional formatting google sheets
  • Text ends with – the cell value ends with the defined input
    text ends with conditional formatting google sheets
  • Text is exactly – the cell value exactly matches the defined input
    text is exactly google spreadsheet conditional formatting

Date Rules

Date rules are another key rule set for conditional formatting in Google Sheets. There are three types of date rules in Google spreadsheets. For Date rules, we recommend using the DATE function to input dates:

=DATE(year, month, day)

This makes entering dates more seamless. For the dataset below, let’s apply conditional formatting to the date “11/01/2022”.

Select A2:A32 at the data range. Choose the Date is rule and Exact date under the Format cells if… section. Use the DATE function to enter the specified date (11/01/2022) into the input box.

=DATE(2022, 1, 11)

date is exact data conditional formatting

The result: conditional formatting is applied to the date 11/01/2022 in the dataset.

Over 50K pros building reports use Coefficient to automate business systems data into their Google Sheets
"What an amazing app! Coefficient makes integrating some of the most complex systems unbelievably simple! Set up your integrations in less than a minute."
Yonatan Schvimer

We leveraged the exact date filter in this example. But there are several other filters we can use, including:

date rules conditional formatting google sheets

Here are the other available Date rules for conditional formatting in Google Sheets.

Conditional formatting is applied if…

  • Date is – the cell date value exactly matches the defined date
    date is conditional formatting
  • Date is before – the cell date value is smaller than the defined date
    date is before conditional formatting google sheets
  • Date is after – the cell date value is greater than the defined date
    exact date is after google sheets

Number Rules

Number rules allow you to apply conditional formatting based on mathematical comparisons and ranges, such as greater than, less than, or in between.

Let’s say in the dataset below we want to format dates that fall between a certain date range: January 15th and January 26th.

Open Conditional format rules. Choose the Date column as the data range (A2:A32). Now, under Format rules, set the following parameters:

Format cells if… 

Is between

=DATE(2022, 1, 15)

and

=DATE(2022, 1, 26)

format cells if is between google spreadsheet

This is what the result looks like: the dates within the range turn light green. Notice that the date range is inclusive (includes both the min and max dates).

Now let’s review all the number rules available in Google Sheets.

Conditional formatting is applied if…

  • Greater than – the cell value is greater than the defined input
    Greater than google sheets conditional formatting
  • Greater than or equal to – the cell value is greater than or equal to the defined input
    greater than or equal to conditional formatting
  • Less than – the cell value is less than the defined input
    less than conditional formatting google sheets
  • Less than or equal to – the cell value is less than or equal to the defined input
    less than or equal to google sheets conditional formatting
  • Is equal to – the cell value is exactly equal to the defined input
    is equal to conditional formatting google sheets
  • Is not equal to – the cell value is not exactly equal to the defined input
    is not equal to google sheets
  • Is between – the cell value is between the two defined inputs (inclusive on both ends)
    is between formatting google spreadsheet
  • Is not between – the cell value is not between the two defined inputs (inclusive on both ends):
    is not between formatting google spreadsheet

Custom Formula

Conditional formatting is most powerful when it is determined by a custom formula.

To set a custom formula, launch Conditional format rules the same way as always, by clicking Format > Conditional formatting.

Under Format cells if…, select Custom formula is.

custom formula google spreadsheet formatting

We’ll start with a simple example. Let’s say we want to highlight the rows where Ross sold an item. The formula we’ll input is:

=$B2=“Ross”

We’ll use the absolute reference symbol ($) to freeze the column we’re checking (“Employee” – column B). Conditional formatting is only activated when the value in column B = “Ross”.  

And with the data range set at A2:E32, the custom formula will apply conditional formatting rules to the associated rows.

highlight rows conditional formatting

The result: all rows linked to Ross are highlighted in light green.

Now let’s say we want to assign a different color to each row depending on the associated employee.

Navigate to Conditional format rules and select Custom formula is under Format cells if… Enter the formula from the last example:

=$B2=“Ross”

Choose a unique color under Formatting style and press Done.

Now create the same conditional formatting rule for each listed employee.

=$B2=“Monica”

=$B2=“Rachel”

=$B2=“Phoebe”

=$B2=“Joey”

=$B2=“Chandler”

For each rule, make sure to select a unique color under the Formatting style section. You can see the result below: each row associated with the employee is highlighted in a distinguishable color.  

highlight employees conditional formatting

Now we can try a more advanced example. Let’s group sales based on the week they were made.

First, to make the process easier, add a Week Number column to our table, using a WEEKNUM formula:

                

conditional formatting guide google sheets

Then add a helper column (column H) with the following formula:

hidden column conditional formatting

Column H should continue all the way down to H32. This returns a TRUE or FALSE value based on whether or not the week number in the current row matches the week number from the previous row.

conditional formatting google spreadsheet

Now let’s define the actual conditional formatting rule. Set Custom formula is to:

 =$H2

weekly sales conditional formatting

The custom formula groups sales by week in either white or light green. This makes it much easier to grasp sales by week across the month of January 2022.

Google Sheets Conditional Formatting: Make Your Data Come Alive

Conditional formatting in Google Sheets allows you to highlight data and trends in your spreadsheet. From color scale distributions, to advanced custom formulas, conditional formatting visualizes insights in a way that makes it easier for you and your team to analyze Google spreadsheet data.

Harness Coefficient to pull real-time data from your business system into Google Sheets for free. Try Coefficient now to improve data analysis and accentuate the power of conditional formatting in your spreadsheet.    

Wait, there's more!

Connect any system to Google Sheets in just seconds.

Get Started Free
50,000+ users on
Google Marketplace
Trusted by thousands
of companies