Say you have a set of numbers, varying over a wide range.
You want to immediately know which are the top 5 numbers among all, or say top 3, or say bottom 5 or bottom 3.
In the normal scenario, you would
  1. Sort in ascending order if you want to find the top 3 or 5 numbers and see them; or sort in descending order if you want to find the bottom 3 or 5 numbers and see them.
  2. Then you would copy paste the numbers or remember it.
  3. Then again you would have to resort to the original way the data was sorted.

Lot of things to do?

Let’s simplify your work.

  1. Simply select all your numbers (without the header of the column, if any) and go to the Home tab on the Ribbon.
  2. From there, Click on Conditional Formatting which is under the Styles section.
  3. Then click on Top/Bottom Rules, and select Top 10 items (for Top values selection) or Bottom 10 items (for Bottom values selection). Don’t worry, the 10 is not fixed, you can make it whatever you want.
  4. Select the kind of formatting you want, to highlight the cells as per the condition.

The best part is that once you set your rules, they are dynamic in nature.

Meaning that suppose based of your values, 3 particular Top value cells were highlighted, but then some numbers change and those 3 are no more the Top values, Excel automatically changes the highlighting and still shows you the Top 3 values.

Enjoy Excel-ing.. As always 🙂