Conditional formatting in spreadsheets

Conditional Formatting allows you to change the color and font emphasis in cells based on conditions you set, so you can identify key points at a glance.

Create a new rule

To add a rule in your spreadsheet:

1

Select the cells you want to format. These cells can be neighboring or detached⁠—hold down the CTRL key to select individual cells.

2

From the Edit toolbar, click the Conditional Formatting icon.

3

Select Add New Rule from the dropdown menu to open the Conditional Formatting panel.

To set conditions for your rule:

1

Open the When dropdown to select a conditional statement. You can create rules based on value, date, or formula.

2

Type a value in the text box.

3

In the Then dropdown, select how to format the cell. You can change the background color, text color, or text emphasis.

4

Click Save Rule to apply the rule to your selected cells.

You can create more complex conditions using the Add Another Condition button. Additional conditions have two settings:

• And allows you to add additional When statements to one formatting condition.
• Otherwise lets you add multiple formatting options to one rule.

In the example below, when cells A2:D20 contain a value greater than $100,000.00, the cells have a yellow fill and display as bold text. Cells with values less than $100,000.00 have a red fill and display in plain text.

Manage rules

After you create a formatting rule, you can edit it by choosing the Manage Rules option under the Conditions menu.

The Conditional Formatting panel shows the rules applied to your current selection or current sheet. You can add, remove, or edit these rules from this panel or clear all rules by choosing Clear Entire Sheet from the Conditions dropdown.

To modify an existing rule:

1

From the Conditional Formatting panel, hover over the rule and click the arrow.

2

Select Edit from the dropdown menu.

3

Apply the necessary edits and click Save Rule to update the rule.

To apply an existing rule to a new selection:

1

Select the cells you want to apply the rule to.

2

From the Conditional Formatting panel, hover over the rule and click the arrow.

3

Click Apply to Selection from the dropdown menu.