conditional formatting | chapter 2 | icon set | top 10 items | bottom 10 items

        Conditional Formatting Chapter 2 

 

Top Bottom Rules

Today we are going to understand several 'Top Bottom Rules'- 

Top 10 items: 

Under this rule excel highlights Top 10 items from your selected data range.

You can increase or decrease the basis of selection. For instance you can select top 5 from your selection.

How top 10 works?
Simple logic with unique values.

With unique values

But there is a trick when there are ties or duplicate values. Lets understand the logic with the help of a video.

 

Top 10% 

Explained with the help of an example below

Bottom 10 Items 

Similar to Top 10 items. Read above.

Sometime it is hardly possible to identify bottom 10 or top 10 values at a glance. 

Excel is absolutely smart in this matter. (Look below)

Bottom 10% 

Bottom 10% rule is also similar to the Top 10% rule as discussed above. Here instead of top values excel deals with lower values in the data range.

Above average and Below Average 

This two option works with simple logic. Just highlighting values above and below average.
We can combine the "above average" and "below average" in a same data range by changing the color of the respective highlights. In the display below we opted separate data range for easy understanding.

Above average and Below average

Data Bars 

 

Data bar options displays the data the data in a range by applying color gradient on the cell. It is sometimes visually pleasing. Without delving into the details we can at a glance understand the present status of the data in the order of comparison. Say small value gradient bar is light and small. High value gradient bar is dark and long. Gradient means gradual. Instead of gradient we can use solid fill.

Color Scales 

Color scales displays the transition of color between two values in comparison.


In the above display we can see lowest value of the range is 1028 and highest value 9979. And 1028 is pinned with solid Red and 9979 is highlighted with solid Green. Now observe the transition of colors of the remaining cells that falls within the two extreme points (1028 & 9979). If we observe very carefully not a single color is matching with any other color in the data range. Though the data range doesn't have any duplicates. If there are duplicates the color will match.

Icon Set 

 

In the Icon set function we will manage rules. That is we are going to customize the rules at our discretion. It is to be noted all the rules can be managed.
We will understand with the help of a video.