Select the numbers, go to Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items, change 10 to the count you want, pick a format and click OK. Use Bottom 10 Items for the lowest values.
Top 10% works on the number of cells, not the size of the values. In a list of 50 numbers it highlights the 5 largest, and 20% highlights the 10 largest. Above Average and Below Average are in the same menu.
To highlight whole rows, or to take N from a cell, use New Rule → Use a formula to determine which cells to format with =$C2>=LARGE($C$2:$C$100,$F$1), where F1 holds the number of values to highlight. Use SMALL and <= for the bottom values.
Select a single column before you apply the rule. With several columns selected, Excel ranks every cell in the block together. Tied values are all highlighted, so a Top 10 rule can colour more than 10 cells.
The best way to highlight top-bottom values is by using conditional formatting. It’s quick and simple, and all you need is a few clicks. But, the best part is, that you don’t even have to write a formula for this.
I have been using this since I got my first job. Whenever my boss called me with some sort of report, I do it for sure to highlight the top and bottom values. And, if you are not using it yet, let me tell you how to use it.
Below, I have a table with day-wise sales and I want to highlight the top 10 days with the highest sale (The table is a bit long here).
Highlight Top 10 Values
- First of all, select the data from which you want to highlight top values.
- Go to Home Tab -> Styles -> Conditional Formatting -> Top/Bottom Rules -> Top 10.
- Now, in the pop-up window, you have two things to specify.
- Numbers of values to highlight (top 10 for now).
- Formatting to use for highlighting values.
- After that, click okay.
It will instantly highlight the top 10 values from the data with the formatting style you have mentioned.
Highlight Bottom 10 Values
For highlighting the bottom 10 values, you can use the same steps. But instead of the top 10, just click on the bottom 10, and the rest of the things are the same.
- Select the numbers of values to highlight.
- And, the format to use.
What Else?
In the top/bottom rule option in conditional formatting, you have some other useful options.
- Top 10% – Use this to highlight the top values which are equivalent to 10% of the data.
- Bottom 10% – Highlight the bottom values which are equivalent to 10% of the data.
- Above Average – For highlighting values that are above the average value of the data.
- Below Average – Use this to highlight values that are below the average value of the data.
As I already said, I highlight top and bottom values in all my reports. And this also helps the other person to get a quick insight, even if the data is just a table.
Hi… sir please answer me one thing that on what principal or formula top 10% or bottom 10% work and if we take 20% instead of 10% then what happens.