“Format only top or bottom ranked values” rule is used to highlight the Top n items or Bottom n Items. We also can highlight Top n% items or Bottom n% Items.
To apply “Format only top or bottom ranked values” rule below are the steps
- Select the range on which you want to apply the conditional formatting
- As in below image select “B2:G9”
![Data sets to apply the conditional formatting](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Comditinal-Formatting-1.jpg)
- Go to Home tab>>Conditional Formatting>>New Rules
![New Rules Option in conditional Formatting](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-32.jpg)
- New formatting rule window will be opened.
- Select “Format only top or bottom ranked values“
There are two type of rule can be applied – Top and Bottom
1.Top:
- Select Top and put the item count
- If you want to highlight Top n% then tick the check box “% of select range“
- Here we are highlighting Top 10 items
![Highlight Top 10 Items](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-60.jpg)
- click on Format button.
- Format Cells window will be opened.
- Give the formatting which you want to apply on the excel cells
![Format Cells Window](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-61.jpg)
- Click on OK button to apply this conditional formatting.
- Data set with this conditional formatting will look like below image.
![Data Sets after applying the conditional formatting](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-62.jpg)
1.Bottom:
- Select Bottom and put the item count
- If you want to highlight Bottom n% then tick the check box “% of select range“
- Here we are highlighting Bottom 10 items
![Bottom Items Window](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-63.jpg)
- Click on OK button to apply this conditional formatting.
- Data set with this conditional formatting will look like below image.
![Data Sets after applying the conditional formatting](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-64.jpg)