Excel Conditional Formatting Top/Bottom Rules

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.

Conditional Formatting Top/Bottom Rules Menu 2010

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

Conditional Formatting Top Ten Dialog Box

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


 ABCD
1Month 1Month 2Month 3Month 4
220605020
360302030
430307070
580204010
650901050

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.



Continue to How to Use the Excel Conditional Formatting Data Bars, Color Scales and Icon Sets  >>