Conditional formatting helps you visually explore and analyze data, detect critical issues, and identify patterns and trends. It makes it easier to highlight interesting cells or a range of cells, emphasize unusual values, and visualize data by using color scales.
A conditional format changes the appearance of cells (background color, font color, font style, borders, prefix and suffix) on the basis of conditions that you specify. If the conditions are true, the cell range is formatted; if the conditions are false, the cell range is not formatted. Conditional formatting is supported for Numbers, Text, Booleans, and Dates. There are many built-in conditions that can be applied to create rules.
Table of Contents
October 2023 update - Now supports Text, Date, and Boolean data types along with numeric.
Applying a Rule
- Right click on the column or row you want to highlight
- Select Conditional Formatting
- Select a condition from the menu.
For example, this could be Empty or Not Empty. Depending on the condition value, you can select the data type values you wish to apply it to. There are different conditions depending on your data type. - Confirm the Value type and Condition.
- Enter the value you wish to apply the formatting to.
- Select the different formatting options you wish to apply.
- If applicable, select the cells where you want to display this formatting.
- Finish by clicking Done
Conditional Formatting Rules
| Option | Definition |
| Empty | Format cells that are empty |
| Not empty | Format cells that are not empty |
| Lower than | Format cells that are lower than a <number> |
| Lower than or equal to | Format cells that are lower than or equal to a <number> |
| Greater than | Format cells that are greater than a <number> |
| Greater than or equal to | Format cells that are greater than or equal to a <number> |
| Equal to | Format cells that are equal to a <number> |
| Not equal to | Format cells that are not equal to a <number> |
| Between | Format cells that are between a <number1> and <number2> |
| Not between | Format cells that are not between a <number1> and <number2> |
| Color scale | Format either the cell text or the cell background by using a gradation of two colors with <number1> as min and <number2> as max. You can choose to add a midpoint <number3> to use a three color gradation. The shade of the color represents higher or lower values. |
Formatting options
There are different formatting options that you can apply to cells that meet the conditions set in the rule.
- Background and Text color. You can link two colors by clicking the Lock icon. If the lock icon is on, the text and the background are used together in future selections. Click the Lock icon to unlink the associated colors if you prefer a different customization.
- Font. Select B for bold and I for italic formatting.
- Cell borders. Applies a border to selected cell walls. The selection is applied to each individual cell that is highlighted.
Managing or Editing the order on which Conditional formatting rules apply
The order in which conditional formatting rules are evaluated also reflects their relative importance: the higher a rule is on the list of conditional formatting rules, the more important it is.
This means that in cases where two conditional formatting rules conflict with each other, the rule that is higher on the list is applied and the rule that is lower on the list is not applied.
To reorder the rules, use the Format panel > Tab Conditional
Editing Conditional Formatting Rules
Open the Format Panel and under the Conditional tab, select the rule you want to edit.
Clearing Conditional Formatting Rules
Use the Conditional Format Panel to delete the unwanted rules. Click on the Delete icon that appears next to the rule to remove it.