How to Use Conditional Formatting in Google Sheets

Change background color and add a professional touch

By

Updated on February 16, 2021

What to Know

Desktop: Select cell range > Format > Conditional Formatting. Enter format rules, formula, and formatting style, then choose Done.
Android: Select cell range > Format > Create a rule > Conditional Formatting. Enter rules, formula, formatting style. Choose Save.
Delete a rule: Hover the cursor over the rule and select the trash can icon (desktop) or tap the trash can icon (mobile).

Conditional formatting in Google Sheets lets you add a professional touch to your spreadsheets by modifying the look and feel of cells, rows, or columns based on certain criteria, or find specific data types to highlight duplicates. Here's how to apply conditional formatting in Google Sheets on a computer or Andriod device.

How to Use Conditional Formatting on a Desktop Browser

Conditional formatting means that when specific conditions are met, the background and text color in designated Google Sheets cells instantly changes. This is useful when you want to see certain information or call out specific data.

Here's how conditional formatting works for Google sheets on a Windows PC or Mac using Chrome, Firefox, Safari for Mac, or IE 11 and Edge for Windows.

Google Sheets may work in other browsers, but all features may not be available.

Select the cell range where you want to apply conditional formatting.

This example uses a spreadsheet with salespeople's conversion rates.
Select Format from the top menu bar.
Select Conditional Formatting.
The Conditional format rules dialog box appears on the right side of the screen.
Select the Format cells if drop-down menu and choose a condition. If you're following this example, choose Less than.

Choose from a variety of self-explanatory conditions, or select Custom to create a condition.
In the Value or formula box, enter the condition's criteria. For this example, enter 30% to highlight salespeople whose conversion rates are less than 30%.
Under Formatting style, select a predefined background color or select Custom format to choose colors and effects, including bold and italics.
To further enhance a conditional effect, select the Color Scale tab.
Select a gradient. The color on the left applies to the lower number values in the selected cell range. The color on the right affects higher values.

You'll see a live preview of the gradient colors when you select a color scale.
When you're happy with your conditional formatting choices, select Done. The spreadsheet reflects your settings.

To apply multiple formatting conditions to the same cell range, go to Format > Conditional Formatting, and select Add another rule. Google Sheets processes multiple rules in priority order from top to bottom. Reorder rules by dragging a rule up or down in the list.

How to Use Conditional Formatting on an Android Device

Here's how conditional formatting works for Google Sheets on an Android device.

Launch the Google Sheets app and open a new or existing spreadsheet.
Select the cell range you want to format.
Tap the Format button, represented by the letter A, near the top of the spreadsheet.
You'll see the Create a rule interface. Scroll down and select Conditional Formatting.
Select the Format cells if drop-down menu and choose a condition.
Adjust the visuals you want to apply to cells that meet your condition. In the Formatting style section, tap one of the six options or choose Custom to select colors and effects.
Tap the Color scale tab to apply gradient colors to cells. Choose the numeric values and colors you want to use.
Tap Save to apply your choices. You'll see your new rule on the Conditional Formatting screen. Tap the check mark to exit and return to the spreadsheet.

Tap Save and New to add another rule.

How to Use Custom Formulas

Google Sheets provides more than a dozen formatting conditions dealing with text strings, dates, and numeric values. You're not limited to these default options. Use a custom formula to apply a condition to a cell range based on values from other cells, which isn't an option with the predefined selections.

This example uses a custom formula to show when the same value appears in more than one cell using the COUNTIF function.

Open a spreadsheet and select the cell range you want to format. This example selects cells B2 through B15.
Go to Format > Conditional Formatting.
Under Format cells if, choose Custom formula is.
Enter the formula into the Value or Formula field. For this example, use the formula:

=COUNTIF(B:B,B2)>1

If your range of cells isn't in column B, change it to your column, and change B2 to the first cell in your selected range.
Select Done. Any duplicate information in your spreadsheet is highlighted.

How to Remove Conditional Formatting on a Desktop Browser

It's easy to delete a conditional formatting rule.

Select the cell range where you'd like to remove one or more conditional formatting rules.
Select Format.
Choose Conditional Formatting.
You'll see any current conditional formatting rules. To delete a rule, hover the cursor over the rule and select the trash can icon.

How to Remove Conditional Formatting on an Android Device

Select the cell or cells where you'd like to remove one or more conditional formatting rules.
Tap Format (represented by the letter A).
Select Conditional Formatting.
You'll see a list of current rules. To delete a rule, tap the garbage can icon next to it.

Was this page helpful?

Thanks for letting us know!

Tell us why!