How to Use Conditional Formatting in Google Sheets

Applying conditional formatting to your spreadsheets can really add a professional touch, modifying the look and feel of cells, rows and/or columns on-the-fly based on certain criteria. When specific conditions are met, the background and text color of the cells in question or even cells elsewhere in your spreadsheet can instantly change. This can be useful in a number of ways, some which are detailed in the examples within this tutorial.

Follow the instructions below to apply conditional formatting in Google Sheets on a computer as well as on an Android device. While you can view conditional formatting rules on iOS devices (iPad, iPhone, iPod touch), you cannot create or edit them.

Basic Conditional Formatting Rules

Adding basic conditional formatting rules to a cell range in Google Sheets all starts with the Format menu.

Desktop/Laptop (most web browsers; Google Chrome preferred)

  1. Select one or more cells where you want to apply conditional formatting. In this example, we've chosen each salesperson's conversion rate.
  2. Click on Format, located in the Sheets menu towards the top of the screen.
  3. When the drop-down appears, choose Conditional formatting.
  4. The Conditional format rules interface should now be displayed on the right-hand side of your spreadsheet, containing a number of configurable settings related to the formatting of the selected cells -- which are represented in the Apply to range section. Select the drop-down menu labeled Format cells if..., which offers several self-explanatory formatting conditions that can be applied to the aforementioned cell range. For the purposes of this example we've chosen to format the cells if their contents equal Less than a 30% conversion rate.
  5. Now that you've specified the condition, it's time to adjust the visuals that you'd like to apply to the cell(s) if it is met. Click on the drop-down menu labeled Formatting style, at which point you'll be presented with a number of predefined text and background colors to choose from. If none of these fits the bill, the Custom format option allows you to select your own colors and effects including bold, italic, underlined and strikethrough text.
  6. If applying just one formatting style to a cell isn't enough to meet your needs, Sheets can display different shades of a particular color depending on how close to the minpoint, midpoint or maxpoint a number or percentage is. To apply this progressive shading to the background of your cells, click on the Color scale tab header in the Conditional format rules interface and enter the desired numeric values and colors in the fields provided.
  7. As you make the above changes, you'll notice that they are applied instantly to the chosen cell range. These changes are not permanent, however, and can be reverted by clicking on the Cancel button. If you'd like to keep them in place, select Done to close the Conditional format rules window and commit your new formatting.

You can apply multiple formatting conditions to the same cell range by repeating steps 1-3 above and selecting the Add new rule option. When multiple rules are applied to the same cell, they are processed in priority order from top to bottom. They can be reordered by simply dragging them up or down within the list.

Android

  1. Launch the Google Sheets app.
  2. Open a new or existing spreadsheet.
  3. Select one or more cells where you want to apply conditional formatting.
  4. Tap the Format button, represented by the letter 'A' and located towards the top of the spreadsheet.
  5. The formatting interface should now be visible at the bottom of your screen. Scroll down and select Conditional formatting.
  6. The Create a rule interface should now be displayed, containing a number of configurable settings related to the formatting of the selected cells -- which are represented in the Apply to range section. Select the drop-down menu labeled Format cells if..., which offers several self-explanatory formatting conditions that can be applied to the aforementioned cell range.
  7. Once you've specified a condition, it's time to adjust the visuals that you'd like to apply to the cell(s) if it is met. Tap one of the six options found in the Formatting style section. If none of these meets your needs, the Custom button allows you to select your own colors and effects including bold, italic, underlined and strikethrough text.
  8. If applying just one formatting style to a cell isn't enough, Sheets can display different shades of a particular color depending on how close to the minpoint, midpoint or maxpoint a number or percentage is. To apply this progressive shading to the background of your cells, tap the Color scale tab header and choose the desired numeric values and colors in the fields provided.
  9. If satisfied with your selections, tap the SAVE button to apply them. If you'd like to add a second rule (conditions described above), select SAVE AND ADD NEW instead.
  10. The Conditional Formatting screen will now appear, listing your new rule(s). Tap the check mark in the upper left-hand corner of the screen to return to your spreadsheet.

Conditional Formatting Using Custom Formulas

conditional formatting custom formula Google Sheets
Screenshot from Windows

Google Sheets provides over a dozen different formatting conditions dealing with text strings, dates and numeric values -- as we highlighted above. This functionality is not limited to these default options, however, as you can also utilize your own formula to determine whether or not a cell range should be formatted.

To do so you need to follow the same steps as you would to incorporate a basic condition, with one key exception. When you reach the point of selecting an option from the Format cells if drop-down menu, choose Custom formula is instead. Next, enter the desired formula in the edit field below the menu.

One of the nice things about using a custom formula is that you can apply conditioning to a cell range based on values that reside elsewhere in the current spreadsheet. In this example I've applied the following formulas to each of the respective cells in column E, coloring the cell green only if the conversion rate found in the preceding column is higher than 40%: =$D2>0.4, =$D3>0.4, =$D4>0.4, =$D5>0.4, =$D6>0.4.

So what we've done here is added conditional formatting to a range of cells that is based on percentages found in a different cell range altogether, something that cannot be achieved with the predefined options.

How to Remove Conditional Formatting

Deleting conditional formatting rules from a cell or group of cells is a very simple process.

Desktop/Laptop (most web browsers; Google Chrome preferred)

  1. Select the cell(s) in which you'd like to remove one or more conditional formatting rules.
  2. Click on Format, located in the Sheets menu towards the top of the screen.
  3. When the drop-down appears, choose Conditional formatting.
  4. The Conditional format rules interface should now be displayed on the right-hand side of your spreadsheet, displaying the rule(s) currently associated with the selected cell range. Hover your mouse cursor over the rule that you wish delete, so that a trash can button appears. Click on this button to delete the associated rule.

Android

  1. Select the cell(s) in which you'd like to remove one or more conditional formatting rules.
  2. Tap the Format button, represented by the letter 'A' and located towards the top of the spreadsheet.
  3. The formatting interface should now be visible at the bottom of your screen. Scroll down and select Conditional formatting.
  4. The Conditional Formatting screen will now appear, listing the rule(s) currently applied to the selected cell range. To delete a particular rule, tap the garbage can button located to the far right of its name. A confirmation message will appear briefly at the bottom of the screen, accompanied by a button labeled UNDO. If you've made a mistake, quickly select this button before it disappears.