How-To

How to Use Conditional Formatting in Google Sheets for Common Tasks

By Sandy Writtenhouse

Published October 9, 2020

You can make your data pop automatically when creating spreadsheets by setting up rules. Here’s how to use conditional formatting in Google Sheets.

If you want to automatically format parts of a Google Sheets spreadsheet, conditional formatting gets the job done. Once you set up the feature, you can make your data pop without lifting another finger.

The nice thing about conditional formatting is that you have flexibility. You can base your conditions on text, dates, numbers, and even blank cells. And you choose the exact way to format it based on those rules. Here’s how to use conditional formatting in Google Sheets for everyday tasks.

Conditional Formatting For Empty Cells

Depending on the type of data you track in your spreadsheet, spotting cells that are left blank but shouldn’t be can be quite useful. And of course, there are instances when the opposite can be helpful too.

A good example for using conditional formatting for empty cells is grade tracking. Whether you’re a teacher tracking test scores or a student logging your own grades, missing one can have adverse effects.

Follow these steps to apply conditional formatting rules for cells that are left blank.

1. Sign into Google Sheets, open your spreadsheet, and select the cells where you want to apply the formatting.
2. Click Format from the menu and pick Conditional Formatting.
3. In the right-hand sidebar, make sure the Single Color tab is selected.
4. Under Apply to Range, you’ll see the range of cells you selected to confirm or adjust.
5. Under Format Rules, select Is Empty in the Format Cells If… drop-down box.
6. Under Formatting Style, choose the format for the blank cells. Since the cells are empty, text formatting isn’t logical. So picking a fill color for the cells works best. Click Default to choose a common color or use the Fill Color button for a custom color.
7. Click Done when you finish.

Give your rule and formatting a test by entering data into one of the blank cells. You should see the fill color disappear.

You can also do the reverse if you have a spreadsheet that should contain empty cells and you want to spot those filled with data instead. Follow the same steps but pick Is Not Empty in the Format Cells If… drop-down box.

Conditional Formatting For Text

Another conditional formatting rule in Google Sheets that’s practical is for text. And you have a handful of options. You can format text that contains or does not contain, starts with, or ends with, or is exactly as the value you enter for the rule.

An example you may relate to is the text that does not contain the @ (at) symbol for email addresses. You might receive a data dump that has customer or client email addresses. Before you set up a mail merge, you’ll need to remove those addresses that are invalid; they do not contain the @ (at) symbol.

1. Follow the same steps above, 1 through 4, for selecting the cells and opening the tool.
2. Under Format Rules, select Text Does Not Contain in the drop-down box.
3. In the box directly below, enter the Value or Formula. For our example, we enter @.
4. Under Formatting Style, choose the format for the cells. This time, you can apply any style of formatting you like since the cells will contain data. For instance, you can color the cell or the text in it, make the text bold, italics, or underlined, or strike through it.
5. Click Done when you finish.

Using text rules for conditional formatting gives you tons of options. Just pick the one that applies to your particular spreadsheet in the Format Cells If… drop-down box.

Conditional Formatting For Dates

One common piece of information in many spreadsheets is the date. And that’s why this conditional formatting rule is a popular one. Automatically format text based on an exact date, one before another, or one after another.

A terrific use for this rule is home budgeting and/or bill tracking. If you keep a spreadsheet for your monthly budget with due dates for bills, you can quickly see those you have coming up. Here, you’ll use the Date Is After condition.

1. Follow the same steps above, 1 through 4.
2. Under Format Rules, select Date Is After in the drop-down box.
3. In the drop-down box directly below, pick Tomorrow (or Today).
4. Under Formatting Style, choose the format for the cells.
5. Click Done when you finish.

Now each time you open this spreadsheet, bills with due dates after tomorrow (or today if you prefer) will jump right out so you can prepare or pay them.

You can look at the other date rules available for different situations. And, check out our tutorial to create a calendar in Google Sheets for another date-related feature.

Conditional Formatting For Numbers

If there’s anything more common in a spreadsheet than a date, it’s a number. Whether it’s a standard number, currency, percentage, or decimal, you will likely find it in a spreadsheet.

One thing many people use spreadsheets for these days is weight tracking. Maybe you do too and log your weight each day or week. And if so, you probably have a goal for the weight or a range you want to stay within. Or perhaps you track your calorie intake and want to keep an eye on those numbers.

For simplicity, we’ll use formatting to show weight in pounds above a certain number.

1. Follow the same steps above, 1 through 4.
2. Under Format Rules, select Greater Than in the drop-down box.
3. In the box directly below, enter the Value or Formula. For this example, enter the target weight.
4. Under Formatting Style, choose the format for the cells.
5. Click Done when you finish.

And be sure to keep the other number rules for conditional formatting in mind: greater than or equal to, less than or less than or equal to, is equal to or not equal to, and is between or not between.

Spot Your Data At a Glance in Google Sheets

With conditional formatting in Google Sheets, you can quickly and easily spot the data you need at a glance. You don’t have to search, sort, or filter your sheet to see important data. Conditional formatting in Excel may feel a bit more robust, but the options in Google Sheets may be exactly what you need.

And if you’d like help with that final rule you see in the Format Rules drop-down box for Custom Formula Is, take a look at our example use for highlighting duplicates in Google Sheets.