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 – Top Bottom Rules in Excel
change formatting of cell with reference to its position ( ranking / performance with reference to average) in set of data
- 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.
- Select Data and click on Conditional Formatting button under Home tab.
- Select Top/Bottom Rules -> Below Average
- New Window will come up with option for color with which you want to highlight. Click OK if default options are fine for you.
- Final Table come as below.
Part 1 of this post also may interest you Conditional Formatting in Excel – Highlight Cell rule