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

  1. Highlight the cell or range of cells that you want to apply formatting rules to.
  2. Click the Format menu and select Conditional formatting….
  3. In the window that appears, define the conditions for specific text colors and/or cell background colors.
  4. Click Save rules.

You can apply multiple rules and select up to five for a given cell or range. To remove a rule, just click the "X" to the right of that rule.

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.
  • 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.
  • There are no rule limits per cell or range in the new Google Sheets.

Advanced conditional formatting in the new Google Sheets

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. Click the Format menu and select Conditional formatting....
  2. Switch to the "Custom formula" option in the drop-down menu.
  3. Add in the relevant formula, rules, and cell range.
  4. Click Save rules.

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.

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

In addition, if you are using the “Text contains” or “Text does not contain” fields while formatting, you can now use wildcard characters.

  • 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”.