Conditional Formatting in Excel
Related Pages:
Conditional Formatting Highlight Cells RulesConditional Formatting Top/Bottom Rules
Data Bars/Color Scales/Icon Sets
Conditional Formatting Using Formulas
Version of Excel
This page describes conditional formatting in current versions of Excel (2007 and later).
Excel Conditional Formatting can be used to alter the formatting of an Excel cell based on either:
- The value of the current cell;
- The value of another cell in the current worksheet;
- The result of a formula (which may rely on the contents of the current cell and/or other cells in the current worksheet).
The Conditional Formatting Menu
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:
- Highlight Cells Rules - Apply formatting to cells that satisfy a specific condition (e.g. greater than, equal to, Duplicate Values, etc.);
- Top/Bottom Rules - Apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (e.g. above average, within top 10%, etc.);
- Data Bars / Color Scales / Icon Sets - Apply formatting to all cells in the range, depending on their value in relation to one another;
- New rule... - Allows you to specify more complex rules, such as rules that depend on the result of a formula.
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
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:
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.