Conditional Formatting in Excel

Version of Excel
This page describes conditional formatting in current versions of Excel (2007 and later).
If you have Excel 2003, see the separate page on Conditional Formatting in Excel 2003.

Excel Conditional Formatting can be used to alter the formatting of an Excel cell based on either:


The Conditional Formatting Menu


Conditional Formatting Main Menu Excel 2010

Before entering the Conditional Formatting menu, you need to first select the cell(s) that you wish to apply the formatting to.

Next, select the Conditional Formatting option from within the Styles group on the Home tab of the Excel ribbon. Clicking on this option will cause the Conditional Formatting drop-down menu to be displayed (see rightabove).

This menu allows you to select the type of Excel Conditional Formatting that you want to apply to your cell(s). This can be either:

These different conditional formatting types are described in depth on the following pages:

Conditional Formatting Rules:
Highlight Cells Rules
Top/Bottom Rules
Data Bars/Color Scales/Icon Sets
Conditional Formatting Using Formulas

Using Multiple Conditions

Conditional Formatting Manage Rules Option  in Excel 2010

Excel Conditional Formatting allows you to specify multiple conditions, and apply different formatting to each of these. After you have specified your first condition, you can specify further conditions by simply repeating the process for adding a condition.

If you want to view or edit the conditions that have been set so far, select the Manage Rules... option from the Excel Conditional Formatting menu (see rightabove). This shows a list of all rules that have been defined so far.

It is important to understand that the order that the Excel Conditional Formatting rules are listed does make a difference. The condition that is positioned at the top of the list is tested first, and then the next one down, etc.

This ordering process is particularly important when you have conditions that overlap (e.g. A1>10, A1>5). This is illustrated in the 'Common Error' example below.


Common Error

When more than one condition is used for Excel Conditional Formatting, it is important to understand that the conditions are tested in the order that they appear in the 'Rules Manager' window.

Therefore, if we wanted to specify that cells having a value greater than 10 be coloured in red and cells having a value greater than 5 be coloured in orange, the following definition (which applies the test "Cell Value > 5" before the test "Cell Value > 10") would NOT work, as required:

Conditional Formatting with 2 Conditions Error in Excel 2010

This example could be made to work as required by positioning the condition "Cell Value > 10" first and placing the condition "Cell Value > 5" second.


Continue to How to Use the Excel Conditional Formatting Highlight Cells Rules  >>