Chapter-8: Format only top or bottom ranked values

“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
Data sets to apply the conditional formatting
  • Go to Home tab>>Conditional Formatting>>New Rules
New Rules Option in conditional Formatting
New Rules Option
  • 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
Highlight Top 10 Items
  • 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
Format Cells Window
  • 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
Data Sets after applying the conditional formatting

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
Highlight Bottom 10 Items
  • 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
Data Sets after applying the conditional formatting

Next ChapterPrevious Chapter