Conditional Formatting – Top Bottom rules in Excel

Using Conditional formatting in excel formatting of data can be changed with change in data. This guide uses top/bottom rule of conditional formatting. Top bottom rules in Excel has options of top 10 items, top 10%, bottom 10 items, bottom 10%, above average and below average.

Conditional formatting with top/bottom rule in excel

Conditional formatting – Top Bottom Rules in Excel

change formatting of cell with reference to its position ( ranking / performance with reference to average) in set of data

1. Enter data in table format.In our example below we have a list of sold fruits with us. We want items which have sold below average sale to be indicated in red.
   

   top bottom rule in excel

2. Select Data and click on Conditional Formatting button under Home tab. 
   
3. Select Top/Bottom Rules  -> Below Average
   

   Options of top bottom rule

4. New Window will come up with option for color with which you want to highlight. Click OK if default options are fine for you.
5. Final Table come as below.
   

   Final Table

Part 1 of this post also may interest you Conditional Formatting in Excel – Highlight Cell rule