How to Set Up Conditional Formatting in Google Sheets

With conditional formatting in Google Sheets, you can make text or cells stand out based on criteria that you set up. When your inventory falls below a certain amount, a bill has passed its due date, or you have empty cells from missing data, you can make those items easy to spot.

Tip: use similar conditional formatting rules in Excel.

Create a Conditional Formatting Rule in Google Sheets

For each of the rule types described below, follow the same steps to create the conditional formatting rule.

Select the cells you want to apply the rule to, whether a column, row, cell range, or the entire sheet. Click Format -> Conditional formatting to open the Conditional Format Rules sidebar, where you’ll set up the rule.

At the top of the sidebar, select the Single color tab for one text format or cell fill color or the Color scale tab for gradient shading.

Note: use the Single color tab for all rules below, except the last rule for creating a color scale.

Confirm the cells in the Apply to range field, or select the Grid icon to edit the range.

Use these format rules to set up conditional formatting based on text, numbers, dates, empty cells, or a custom formula. You’ll also learn how to create a color scale.

Create a Rule for Text

Perhaps you’d like to see cells that contain specific text stand out, if you’re looking for emails, web links, cities, or names. You can apply formatting to cells with text that contains or doesn’t contain, starts or ends with, or exactly matches certain text.

As an example, we want to see those imported email addresses that are missing the @ symbol.

In the Format cells if drop-down box, select Text does not contain, and enter “@” in the box beneath. In the Formatting style box, choose the cell color or text format. We are selecting a light blue fill color.

Click Done at the bottom of the sidebar to apply the rule.

The email addresses that do not contain the @ symbol are highlighted.

Create a Rule for Numbers

Do you want to spot something like sales below a certain amount, expenses above an amount, or grades between two others? Format cells containing numbers that are greater than, greater than or equal to, less than, less than or equal to, are equal to, aren’t equal to, are between, or aren’t between other numbers.

In this example, we want to highlight inventory that’s less than or equal to 100.

In the Format cells if drop-down box, choose Text does not contain, then enter “100” in the box beneath. In the Formatting style box, select the cell color or text format. We are choosing a red bold font color.

Click Done to apply the rule.

It shows four inventory amounts less than or equal to 100.

Create a Rule for Dates

If you want to quickly see bills that are past due, tasks that are coming due, or items you renewed in the past month or year, use conditional formatting for dates. Choose dates that fall before or after a day or are an exact match to another date.

In this example, we’re looking for tasks that have deadlines after tomorrow.

In the Format cells if drop-down box, select Date is after, and in the drop-down box below, choose tomorrow. Note that you can also choose exact date and enter the next day’s date in the box beneath. This is a good option if you want to keep the date static.

In the Formatting style box, pick the cell color or text format. We’re choosing both a red font and yellow fill color.

Click Done to apply the rule.

It shows three upcoming tasks that are due later than tomorrow.

Tip: automatically add dates to Google Sheets documents. If you have a list, you can also sort by date in Sheets.

Create a Rule for Empty Cells

When you share a Google Sheet with others or perform a data import, you could experience missing data. You may have cells that should contain some form of data but are blank. To quickly spot the blanks, set up a conditional formatting rule to find empty or not-empty cells.

For this example, we imported sales from our nationwide locations but are missing some cities, states, and amounts.

In the Format cells if drop-down box, choose Is empty. In the Formatting style box, select the cell color or text format. We’re choosing a purple fill color.

Click Done to apply the rule.

The data we’re missing can be seen easily.

Create a Rule for a Custom Formula

If you’re familiar with using formulas in Google Sheets, consider taking advantage of that feature in conjunction with conditional formatting. This lets you apply a format to cells based on the formula results.

For a useful example, we have a to-do list set up in Google Sheets using checkboxes for completed items. To make the incomplete tasks easier to see, we’re crossing off and dimming the completed tasks, based on when the checkbox is marked.

In the Format cells if drop-down box, pick Custom formula is. In the box beneath, enter your formula. The formula we are using is =($S2=TRUE), which translates to the box in cell S2 is checked.

In the Formatting style box, choose the cell color or text format. We’re selecting strikethrough and light gray for the font.

Click Done to apply the rule.

Our completed tasks are crossed off and dimmed, while remaining tasks stay the same, to easily see which are incomplete.

Create a Color Scale Rule

One more handy conditional formatting rule uses a color scale. This is ideal if you’re creating a heat map where the colors vary based on the values in the cells. You can have a light shade for lower values and a dark shade for the higher values, or vice versa.

In this example, we’re using a color scale to display our state’s sales in varying colors. Those with the lowest sales appear white, average sales are in pink, and the highest sales are in red.

In the Color scale tab, select Preview to pick the color theme. We are choosing the White to Red scheme.

You can select the low, middle, and high value types using the Minpoint, Midpoint, and Maxpoint drop-down boxes. This lets you choose minimum and maximum values, numbers, percents, and percentiles. Alternatively, you can have Google Sheets determine this automatically.

If you choose one of the number formats, enter the corresponding value to the right. You can also select a particular shade to the right.

Click Done to apply the rule and see your color scale or heat map.

Delete a Conditional Formatting Rule

You can easily delete a conditional formatting rule that was set up in Google Sheets.

Select a column, row, or cell in the formatted range, and go to Format -> Conditional formatting in the menu. Click the Delete (trash can) button next to the rule in the sidebar.

Using the Google Sheets conditional formatting feature, you can save time and your eyes from having to manually scour your sheet for data. Let a rule you set up automatically format the cells or font for you to make that data pop.

Image Credit: Pixabay. All screenshots by Sandy Writtenhouse.