Use conditional formatting rules in Google Sheets

Create a conditional formatting rule

On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
Select the range you want to format.
Tap Format Conditional formatting. A panel will open.
Set your rule conditions. If there's already a rule in the cell or range, to add another, tap ADD first.
- Single color: Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style," choose what the cell will look like when conditions are met. To create your own formatting style, under custom, tap Add .
- Color scale: Under "Formatting style," select the color scale. You can set a minimum, maximum, and midpoint value. If you don’t set a value, it will automatically adjust based on your data.
Tap SAVE.

Example

A teacher can highlight test scores to see which students scored less than 80%.

On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
Select the test scores.
Tap Format Conditional formatting.
Under "Format cells if," tap Less than. If there's already a rule, tap it or ADD Less than.
Tap Value or formula and enter "0.8."
To choose a red color, tap the red box.
Tap SAVE. The low scores will be highlighted in red.

See your conditional formatting rules

On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
Tap Format Conditional formatting.
At the top, choose an option:
- Selected range: Shows the rules that intersect with the selected range.
- All: Shows all rules on the sheet.
To return to your spreadsheet, tap Done .

Use advanced conditional formatting

Use custom formulas with conditional formatting

You can use custom formulas to apply formatting to one or more cells based on the contents of other cells.

On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
Select the cells you want to format.
Tap Format Conditional formatting.
Under the "Format cells if" drop-down menu, tap Custom formula. If there's already a rule, tap it or ADD Custom formula.
Tap "Value or formula" and add the formula and rules.
Tap SAVE.

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 1

To highlight when there's more than one occurrence of the same value in your data:

On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
Select the range you want to format. For example, cells A1 to A100.
Tap Format Conditional formatting.
Under the "Format cells if" drop-down menu, tap Custom formula. If there's already a rule, tap it or ADD Custom formula.
Enter the rule for the first row. In this case the rule would be, "=COUNTIF($A$1:$A$100,A1)>1."
Choose other formatting properties.
Tap SAVE.

Example 2

To format an entire row based on the value of one of the cells in that row:

On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
Select the range you want to format, for example, columns A:E.
Tap Format Conditional formatting.
Under the "Format cells if" drop-down menu, tap Custom formula. If there's already a rule, tap it or ADD Custom formula.
Enter the rule for the first row. For example, if you want to make the whole row green if the value in column B is "Yes", write a formula like "=$B1="Yes"."
Choose other formatting properties.
Tap SAVE.

Absolute vs. relative references

Often, you will need to add dollar signs ($) in front of letters and numbers in formulas so that the formatting is applied using absolute references as opposed to relative references.

Use wildcard characters with conditional formatting

You can use wildcard characters to match multiple expressions. Wildcard characters can be used with the "Text contains" or "Text does not contain" fields while formatting.

To match any single character, use a question mark (?). For example, a text rule containing "a?c" would format cells with "abc," but not "ac" or "abbc."
To match zero (0) or more characters, use an asterisk (*) . For example, a text rule containing "a*c" would format cells with "abc," "ac," and "abbc" but not "ab" or "ca."
To match a question mark or asterisk 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."

Notes:

To remove a rule, tap Remove Done .
Rules are evaluated in the order listed. The first rule found to be true will define the format of the cell or range. To reorder rules, tap Move and drag 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.