How to Use Conditional Formatting in Google Sheets

Change background color and add a professional touch

Conditional formatting in Google Sheets lets you add a professional touch to your spreadsheets. Modify the look and feel of cells, rows, or columns based on certain criteria. Plus, find specific data types. For example, use conditional formatting to highlight duplicate data in Google Sheets to spot trends. Here's how to apply conditional formatting in Google Sheets on a computer or an Andriod device.

While you can view conditional formatting rules on iOS devices, you can't create or edit these rules.

Team members working at a computer applying conditional formatting to Google Sheets
gilaxia / Getty Images

How to Use Conditional Formatting in Google Sheets

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.

Set Conditional Formatting With Google Sheets on a Desktop

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.

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

    This example uses a spreadsheet with salespeople's conversion rates.

  2. Select Format from the top menu bar.

    Select Format from the Google Sheets menu bar
  3. Select Conditional Formatting.

    Select Conditional Formatting from the Google Sheets Format menu
  4. The Conditional format rules dialog box appears on the right side of the screen.

  5. Select the Format cells if drop-down menu and choose a condition. If you're following this example, choose Less than.

    Google Sheets Conditional Format Rules dialog box with Format Tools > Less Than selected

    Choose from a variety of self-explanatory conditions, or select Custom to create a condition.

  6. 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%.

  7. Under Formatting style, select a predefined background color or select Custom format to choose colors and effects, including bold and italics.

    Google Sheets Conditional Formatting style menu
  8. To further enhance a conditional effect, select the Color Scale tab.

    Google Sheets with the Color Scale heading highlighted
  9. 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.

  10. When you're happy with your conditional formatting choices, select Done. The spreadsheet reflects your settings.

    Google Sheets Conditional Format Rules window with Done button

    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.

Conditional Formatting With Google Sheets on an Android Device

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

  1. Launch the Google Sheets app and open a new or existing spreadsheet.

  2. Select the cell range you want to format.

  3. Tap the Format button, represented by the letter A, near the top of the spreadsheet.

  4. You'll see the Create a rule interface. Scroll down and select Conditional Formatting.

  5. Select the Format cells if drop-down menu and choose a condition.

  6. 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.

  7. Tap the Color scale tab to apply gradient colors to cells. Choose the numeric values and colors you want to use.

  8. 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.

Conditional Formatting Using 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.

  1. Open a spreadsheet and select the cell range you want to format. This example selects cells B2 through B15.

    Select the cell range where you want to apply conditional formatting
  2. Go to Format > Conditional Formatting.

  3. Under Format cells if, choose Custom formula is.

    Under Format cells if, choose Custom formula is.
  4. 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.

  5. Select Done. Any duplicate information in your spreadsheet is highlighted.

    Select Done. Any duplicate information in your spreadsheet will be highlighted.

How to Remove Conditional Formatting

It's easy to delete a conditional formatting rule.

Using Google Sheets on the Desktop in a Browser

  1. Select the cell range where you'd like to remove one or more conditional formatting rules.

    Select Google Sheets cells where you want to move a conditional formatting rule
  2. Select Format.

    Google Sheets with the Format heading highlighted
  3. Choose Conditional Formatting.

    Google Sheets with the Conditional formatting command highlighted
  4. You'll see any current conditional formatting rules. To delete a rule, hover the cursor over the rule and select the trash can icon.

Using an Android Device

  1. Select the cell or cells where you'd like to remove one or more conditional formatting rules.

  2. Tap Format (represented by the letter A).

  3. Select Conditional Formatting.

  4. You'll see a list of current rules. To delete a rule, tap the garbage can icon next to it.