How to work with conditional formatting in Google Sheets

What is Google Sheets conditional formatting?

Highlighting specific data with color is a great way to emphasize key information — many of us do this all the time.

But if you have data that changes frequently, like values that rise or fall above a certain number, or cells containing particular keywords, manually formatting each cell can be time-consuming and prone to errors.

Wouldn't it be great if such changes to formatting occurred automatically?

Conditional formatting in Google Sheets does just that. It automatically updates cell colors based on rules you set so that important cells stand out immediately.

Let’s explore some examples so you see how easily Google Sheets can apply and adjust formats based on data in real-time.

How to add a conditional formatting rule

Whatever conditional formatting rule you'd like to add in Google Sheets, it all starts the same — with a few clicks:

1. Select the cells you want to apply the rule to.
2. Go to Format > Conditional formatting in the Google Sheets menu:
3. A panel will appear on the right side of the screen where you’ll set the details of the rule:
   • edit the range to apply the rule to if needed
   • set up the condition that best fits your needs
   • choose how you want the cells to look like when the rule is met: set a background color or/and font color; make the text bold, italic, underlined or strikethrough.
   
4. Once everything's set, click Done to save the rule.

You can add more rules by clicking Add another rule on the same pane. And you can easily modify or remove existing rules anytime:

With this general setup, you’re ready to create specific formatting rules for various use cases. Let’s jump right in 🙂

Set up simple formatting rule for numbers

Conditional formatting in Google Sheets is a fantastic way to visually highlight numerical data based on specific values. So let's suppose you want to color orders greater than $200 in total sales (column F):

1. Select column F (a column with your numbers).
2. Go to Format > Conditional formatting.
3. Choose a condition from the dropdown menu: Greater than or equal to, and enter 200 in the provided field:
4. Set the formatting style you prefer. For instance, use bold red text on a yellow fill color to make the cells stand out:

The style will be immediately applied to all cells meeting your condition: they all change colors accordingly.

As an alternative, for a more nuanced visualization, you can use a color scale:

1. In the Conditional format rules sidebar, switch to the Color scale tab.
2. Pick one of the existing sets of colors.
3. Or customize colors for the minimum, maximum and (optionally) midpoint values. For example, set light green for the smallest numbers and dark green for the largest so that cells gradually darken as values increase:

These are simple yet effective ways to draw attention to numerical patterns in your data 🙂

Format cells in Google Sheets by multiple conditions

In some cases, you may want to apply different formats based on different conditions to the cells in the same column. Let's say you need to color orders over $200 in green and orders under $100 in red to draw attention to these specific amounts.

If color scale is not your best bet, you can try creating several formatting rules, each specific to one criterion:

• Greater than or equal to 200.
• Less than or equal to 100.

All your rules will be applied at the same time. Thus, multiple rules let you create a more tailored view of your data, spot key patterns and take action accordingly.

Google Sheets conditional formatting with custom formulas

While Google Sheets offers standard options to format cells, they may sometimes fall short of covering specific or complex cases where a custom approach is needed. That’s why there's this option to use custom formulas as conditions.

Custom formulas let you build your own rules using standard functions and operators. You'll be able to specify whether a cell meets a particular condition. The result of the formula should be either TRUE or FALSE — if TRUE, the formatting will be applied.

To get started, choose Custom formula is from the drop-down menu in the Conditional format sidebar:

Let me cover some useful examples so you see how this can work, from highlighting the highest values to marking minimum values while excluding zeros.

As you can see, custom formulas add flexibility to conditional formatting in Google Sheets. I’ll return to them often throughout this article to solve other specific formatting cases!

Conditional formatting if cell contains text

To conditionally format cells with text in Google Sheets, use the related rule: Text contains

In addition to just entering the word itself, you can incorporate wildcard characters to enable broader pattern matching for the search:

• Asterisk (*) matches any number of characters. Thus, d* pattern will find dark chocolates.
• Question mark (?) matches exactly one character. Thus, d??? pattern will find dark chocolates.

Custom formulas with REGEXMATCH will also be a good fit here for highly specific searches:

=REGEXMATCH(D2:D50,"Dark")

Google Sheets conditional formatting with dates

Managing deadlines or tracking orders is yet another task you can cover with conditional formatting. Google Sheets lets you set up custom rules to highlight dates within specific time frames or to signal when deadlines have passed.

How to format blank cells

Highlighting cells based on text or numbers is helpful, but what about blank cells? Formatting based on blanks can be useful for tracking data completion or signaling about cells that still need attention.

If you only need to check whether a cell is blank, simply select Is empty or Is not empty in the conditional formatting rules.

But if you want to combine this with another condition, you'll need a custom formula. Here are 2 basic formulas for your conditional formatting in Google Sheets:

• To format blank cells: =""
• To format non-blank cells: <>""

Let's suppose you have an order status in column G and corresponding details in column H. If the status is Ready to Complete but the details are missing, you want to color that blank cell in column H blue to remind you to fill in the necessary information. 

Here's a custom formula for this:

=AND(G2="Ready to Complete",H2="")

This formula checks if the status is Ready to Complete and if column H is empty. When both conditions are met, the formatting will apply.

Use this approach with empty/non-empty cells in other parts of your sheet to highlight missing values or track completion status.

Conditional formatting based on checkboxes in Google Sheets

Checkboxes can help you dynamically change formatting when checked (TRUE) or unchecked (FALSE). This is particularly useful for tracking tasks, to-do lists, and other datasets that rely on a checked/uncheked status.

You'll need custom formulas for this task:

• To format cells where a checkbox is checked, use a formula with TRUE:

  =G2=TRUE

  make sure to apply it to a column with checkboxes

• To format cells where a checkbox is unchecked, use a formula with FALSE:

  =G2=FALSE

How to use Google Sheets conditional formatting to highlight entire row

Conditional formatting in Google Sheets can also format entire rows in your table. Let's see how it works by the example of checkboxes. I'll highlight all related data in a row whenever a checkbox is checked.

1. Select the range you want to format. This should be your entire table (except for header) if you want to color the entire row: A2:G50
2. In your custom formula, you’ll want to create an absolute reference to the column containing the checkbox (e.g., $G) so the formatting rule always refers to this one column. As for the row, let it remain flexible (2) so the rule is applied to each row individually:

   =$G2=TRUE

This automatically formats entire rows whenever the checkbox in column G is checked.

Google Sheets conditional formatting based on another cell

Did you know that you can format certain cells based on values in different cells? This lets you easily change the condition itself (in a cell) without updating the conditional formatting rule directly.

Suppose you want to color rows where Qty per order is less than 50 or more than 100:

1. Start by entering those values in helper cells (for example, in column I next to your table):
2. Open Conditional format rules and set up your whole table (but its header) as a range to format: A2:G50
3. Then goes the formula for your first rule — the one that will highlight orders with quantities over 100:

   =$E2>=$I$3

   Note. Use absolute references ($) when referencing cells outside the table: $I$3. They will make sure that whatever you do with the table, the formula will still refer to this cell.

   
4. Add a second rule to color order with fewer than 50 items. Click Add another rule at the bottom and change your custom formula to:

   =$E2<=$I$2

   

This two-rule approach will highlight largest and the smallest orders based on different conditions, allowing quick updates just by changing values in column I.

Conditional formatting based on another sheet

Placing condition cells like I2 and I3 on a separate sheet can help keep the main sheet tidy. However, Google Sheets doesn’t support direct references to other sheets in conditional formatting rules. Named ranges also won't help here. To solve this riddle, use the INDIRECT function to reference cells across sheets.

Here’s how you set up conditional formatting based on another sheet:

1. Move your condition cells (with the min and max order Qty.) to another sheet. Let’s say they’re now in Sheet2 in cells C2 and C3:
2. In your rule settings, select your original table on Sheet1 as a range to format: A2:G50
3. As for the custom formula, use INDIRECT to reference C2 in Sheet2:

   =$E2<=INDIRECT("Sheet2!C2")

   The INDIRECT function will convert your text string "Sheet2!C2" into a cell reference that your formatting rule can read:

4. Similarly, for the upper limit, add another rule:

   =$E2>=INDIRECT("Sheet2!C3")

With these formulas, you can update conditions on Sheet2, and the conditional formatting on your main sheet will adjust automatically. And no clutter on your main sheet 😉

How to remove conditional formatting in Google Sheets

Removing conditional formatting from your table is just as easy as adding it:

1. Select any cell within the range where you’ve applied conditional formatting.
2. Go to Format > Conditional formatting.
3. On the sidebar that appears, you'll see all the rules applied to the range.
4. Hover over the rule you want to remove and click the trash icon.

This will delete that specific rule without affecting other rules or formats.