Apply conditional formatting rules

Use conditional formatting rules to get a visual summary of complex or changing data. You can format cells to change the text or background colors if they meet certain conditions, for example if the cells contain certain words or a number.

  1. Open a spreadsheet.
  2. Highlight the cell or range of cells that you want to apply formatting rules to.
  3. Click the Format menu.
  4. Select Conditional formatting.
  5. Under “Conditional format rules” on the panel that appears on the right, choose either Single color or Color scale to choose how the conditional formatting will appear. With single colors, you set up the rule yourself and the color of cells changes based on what you choose. With color scales, multiple colors can be applied based on the lowest and highest values.
  6. Define the conditions and formatting based on your selection:
    • Single color: Under "Format cells if...", define the rule that needs to be met. Under "Formatting style", choose from a number of different options for how the formatting will appear when conditions are met.
    • Color scale: Under “Preview”, select the color gradient you want to use. Then, choose a minimum and maximum value, and an optional midpoint value, using the drop-down menus to choose what type of value you want them to be, options that could include:
      • Min value
      • Max value
      • Number
      • Percent - Proportion or fraction of 100 (value divided by max value)
      • Percentile - values below a certain percentage in a statistical distribution
        • Example: If you have a test with 100 possible points and the person with the highest score gets 50 points, this is 50 percent but the 100th percentile.
  7. Click Done.

To remove a rule, just click the trash icon that appears when you hover over a rule in the rule list.

Notes:

  • When you specify multiple rules, they are evaluated in the order listed. This means that the first rule found to be true will define the format of the cell or range. You can click and drag rules to reorder them.
  • If you copy and paste from a cell or range that has formatting rules, these rules will be applied when you paste the copied data.
  • In the classic Google Sheets, you can apply multiple rules and select up to five for a given cell or range.There are no rule limits per cell or range in the new Google Sheets.

Advanced conditional formatting

Use custom formulas with conditional formatting

In the new Google Sheets, you can also apply conditional formatting using a custom formula. This allows you to apply formatting to a cell or range of cells based on the contents of other cells. To format using your own formula:

  1. Open a spreadsheet.
  2. Highlight the cell or range of cells that you want to apply formatting rules to.
  3. Click the Format menu.
  4. Select Conditional formatting.
  5. Under the “Condition” drop-down menu, choose Custom formula is.
  6. Add in the relevant formula and rules.
  7. Click Done.

As an example, suppose you are a teacher who keeps a spreadsheet containing your students’ names (column A) and the grades they received on their homework (column B). If you wanted to see which students are in the top 20% of the class, you could format the cells by applying the formula =PERCENTRANK($B:$B, B1)>=0.8.

Note: In many cases, you will need to add dollar ($) signs in front of letters and numbers in these formulas so that the formatting is applied using absolute references as opposed to relative references (A1 to B1, A2 to B2).

Use wildcard characters with conditional formatting

In the new Google Sheets, you can now use wildcard characters if you want to match multiple expressions. Wildcard characters can be used with the “Text contains” or “Text does not contain” fields while formatting.

  • Use a question mark (?) to match any single character. For example, a text rule containing “a?c” would format cells with “abc”, but not “ac” or “abbc”.
  • Use an asterisk (*) to match zero (0) or more characters. For example, a text rule containing “a*c” would format cells with “abc”, “ac”, and “abbc” but not “ab” or “ca”.
  • If you need to match a question mark or asterisk exactly in text, you can escape the wildcard characters by adding a tilde (~) in front of them. For example, a text rule containing “a~?c” would format cells with “a?c” but not “abc” or “a~?c”.

Matt is a Docs & Drive expert and author of this help page. Leave him feedback below about the page.

Was this article helpful?
Yes
No