Excel Conditional Formatting Top/Bottom Rules
Related Pages:
Conditional Formatting Highlight Cells RulesData Bars/Color Scales/Icon Sets
Conditional Formatting Using Formulas
The Excel Conditional Formatting Top/Bottom Rules allow you to 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.).
These conditions will only be applied to cells containing numeric values.
The Top/Bottom conditional formatting option is listed in the Excel Conditional Formatting menu, which is generally located in the 'Styles' group of the Home tab on the Excel ribbon (see rightabove).
When you select one of the Top/Bottom Rules from the menu, the 'Top Ten Items' dialog box opens up (see below). This allows you to choose the formatting to apply to cells satisfying the chosen criteria. (e.g. Top Ten Items, Top Ten %, etc).
If you have selected a 'Top Ten' or 'Bottom Ten' option, the dialog box also gives you the option of changing the value of '10' to a different value (e.g. Top 5, Bottom 20, etc).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month 1 | Month 2 | Month 3 | Month 4 |
| 2 | 20 | 60 | 50 | 20 |
| 3 | 60 | 30 | 20 | 30 |
| 4 | 30 | 30 | 70 | 70 |
| 5 | 80 | 20 | 40 | 10 |
| 6 | 50 | 90 | 10 | 50 |
The Top 10%... formatting option has been applied to cells A2 - D5 of the above spreadsheet on the right.
It can be seen that, of the 20 cells in the range A2 - D5, the two cells (i.e. 10% of the cells) with the greatest values have been formatted in pale red.