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 apply color, bold, italic, underline, or strikethrough to cells based on conditions you set.

Creating Rules in Spreadsheets

To create a rule in your spreadsheet, select the cells you wish to add formatting to. Cells can be contiguous, or you can hold down the Ctrl key and select individual cells.

With your cells selected, go to the Edit tab and click the Conditional Formatting icon. Select Add New Rule from the drop down menu.

image alt text

In the Conditional Formatting panel, you will be able to set the logic for your conditional formatting. You can set conditions based on cell value, date, or a formula.

image alt text

Depending on your selection, you may choose to apply formatting when a certain value is above or below a threshold, before or after a certain date, based on a custom formula, or choose from one of the other available options.

You can add more conditions to a rule using the Add Another Condition button, allowing you to create complex conditions for your formatting. Once you have created the rule, be sure to click the green Save Rule button to apply it to the sheet.

NOTE: The order of conditions affects their usage. Make sure they are in the order you want operations to occur, or you may not achieve expected results.

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

image alt text

Managing Rules in Spreadsheets

After formatting rules have been created, you can manage them by choosing the Manage Rules option under the Conditions menu.

In the Conditional Formatting panel you can see the rules applied to your current selection or current sheet if you are in Spreadsheets, or in the entire table in a document. You can add, remove, or edit rules as desired. You can also clear all rules by choosing Clear Entire Sheet under the Conditions icon.

image alt text

You can hover over any rule and then click on the down-arrow. A drop-down menu will give you the options of editing, applying to a selection or deleting the rules. Once your rule is saved, you can see where it’s applied on the current page by hovering over the rule.

You can also view, edit, and delete rules at any time from the Conditional Formatting panel. To do this, hover over a rule and open the action menu, then choose the desired option. If you currently have a portion of your document or sheet selected, you can also apply a rule to your selection from the action menu.

image alt text