Conditional Formatting in Spreadsheets

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

Creating a New Rule

To create 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.

Add New Rule

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

Editing a Rule

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

Formatting example

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

Conditional Formatting panel

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.

Editing a rule

To apply an existing rule to a new selection:

1
Select the cell(s) 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.