How To Do Conditional Formatting In Google Sheets

In this tutorial, you will learn how to do conditional formatting in Google Sheets.

Conditional Formatting in Google Sheets is a powerful tool that allows you to apply formatting to cells based on cell contents or other conditions. It can be used to highlight important items, call attention to values that are out of range and much more

This tutorial covers how to use simple conditional formatting rules and touches on how more advanced rules work so you can begin creating your own custom conditional formatting rules

Intro to Conditional Formatting

Google Sheets comes with a number of preset conditional formatting rules. Here’s how to use them:

Step 1

Select the range you want to apply conditional formatting to and open the Format menu, then choose Conditional Formatting

Step 2

Under Format Cells If, choose one of the available options. For example, you can choose to format cells that contain certain text, empty cells, dates after a given date, etc. For this example, select the Greater Than option

Step 3

Type in the value to check against in the field that appears and you’ll see all values that meet the criteria highlight in the spreadsheet

Step 4

Use the Formatting Style options at the bottom of the Conditional Format Rules sidebar to change the format of those cells, then hit Done to apply the rule

Step 5

Whenever you update the values of cells in the conditional formatting range, Google Sheets will re-check the value to see if it matches the criteria and update the formatting automatically

A Custom Example: Highlighting a Row

In addition to the preset options, Google Sheets also comes with an option to write your own conditional formatting formulas. To get you started using custom conditional formatting rules, here’s an example of a frequently used custom conditional formatting formula

Imagine you have a list of orders and want to call attention to orders listed as URGENT by highlighting the entire row, not only the cell containing the word URGENT Here’s how to do this using custom conditional formatting rules:

Step 1

Select the entire range you want to highlight, open the Format menu and select Conditional Formatting

Step 2

If you already have conditional formatting rules applied to this range, the sidebar will show a list of existing rules. Click Add Another Rule

Step 3

In Custom Formula Is from the bottom of the Format Cells if menu

Step 4

In the Value or Formula field, type the following formula: “=$C1=”URGENT””, making sure to keep the quotation marks around the word URGENT

This formula will be evaluated for each cell in the range. Because of the dollar sign designating an absolute reference to column C, the formula will always check in column C for the word URGENT, but will check the row corresponding to the currently checked cell. The end result is that the formula will evaluate to TRUE in rows where the value in column C is matches the formula value and those cells will be formatted, while other rows will remain unformatted

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to do conditional formatting in Google Sheets. Want more? Check out all the Google Sheets Tutorials.