Apply conditional formatting rules

You can visually show trends in complex or changing data in a spreadsheet using conditional formatting rules. Cells can be formatted to change text or background color automatically if they meet certain conditions, for example if they contain a certain word or a number.

Open a spreadsheet.

Highlight the cell or range of cells that you want to apply formatting rules to.

Click the Format menu.

Select Conditional formatting.

Under “Conditional format rules” on the right, choose either Single color or Color scale. 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.

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

A teacher wants to visually show in a spreadsheet which students struggled on a test (<80%). To do this, the teacher can create a rule using conditional formatting, where the low test scores are highlighted in red. That way, it will be easy to see which students need a bit more attention.

Rule: Test scores <80% highlighted in red

Highlight all the test scores in the column.

Click the Format menu > Conditional formatting.

Under “Format cells if,” choose Less than (or Less than or equal to if 80% is also considered low).

Set the value at 80.

Choose a red color to highlight these cells with.

Click Done. The low scores are now highlighted in red and easy to see.

To remove a rule, just click the Remove rule icon that appears when you hover over a rule in the 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.

Advanced conditional formatting

You can 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:

Open a spreadsheet.

Highlight the cell or range of cells that you want to apply formatting rules to.

Click the Format menu.

Select Conditional formatting.

Under the “Condition” drop-down menu, choose Custom formula is.

Add in the relevant formula and rules.

Click Done.

Note: Formulas can only reference the same sheet, using standard notation (='sheetname'!cell). To reference another sheet in the formula, use the INDIRECT function.

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.

Absolute vs. relative references

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

You can 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”.

Share this:

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