Conditional Formatting: Highlighting Top 10 Percent Values in Excel
This blog post is in continuation to our series of posts on “Conditional Formatting”. In this blog post we will learn how to highlight “Top 10%” values from selected set of values with the help of “Conditional Formatting”.
Conditional Formatting Top 10% option we can find in Styles group under Home Tab.
2nd Input: Formatting Style in which we wish to highlight “Top 10%” values. (In this case we have selected formatting style as “Green Fill with Dark Green Text”.
With these, we can now highlight Top N% values from selected set of values where N can be any integer 1,2,3…. Likewise.
Now the main question is what does it mean by highlighting “Top 10%” values from selected set of values? It means “Conditional Formatting” will highlight Top 10% values from total no. of values.
If our total values are 10 then excel will highlight Top 1 values.
If our total values are 20 then excel will highlight Top 2 values.
If our total values are 30 then excel will highlight Top 3 values.
This is the basic concept behind this option. So let’s learn how we can highlight these Top 10% values using “Conditional Formatting” in excel.
To explain this topic in detail, I have listed total 50 values as shown in below image. We will see how we can highlight Top 10% values from selected set of 50 values.
Select all 50 values and select “Top 10%” option from “Conditional Formatting” as shown in below image.
“Top 10%” dialog box will appear on screen. We have to provide 02 inputs in this dialog box:
1st Input: % value which we wish to highlight (“10%” to highlight “Top 10% values”)
2nd Input: Formatting Style in which we wish to highlight “Top 10%” values. (In this case we have selected formatting style as “Yellow Fill with Dark Yellow Text”.
Once we hit “OK” button, we will see Top 05 values will get highlighted from selected cells in selected formatting style (10% of total 50 values is “5”).
Example 1: Highlighting Top 20% Values:
We have learned how we can highlight “Top 10%” values from selected set of values. Now we will learn how we can highlight “Top 20%” values from selected set of values with this example.
We will use same 50 values which we have used above. Select all 50 values and select “Top 10%” option from “Conditional Formatting” as shown in below image.
“Top 10%” dialog box will appear on screen. We have to provide 02 inputs in this dialog box:
1st Input: % value which we wish to highlight (“20%” to highlight “Top 20% values”)
2nd Input: Formatting Style in which we wish to highlight “Top 20%” values. (In this case we have selected formatting style as “Light Red Fill with Dark Red Text”.
Once we hit “OK” button, we will see Top 10 values will get highlighted from selected cells in selected formatting style (20% of total 50 values is “10”).
Example 2: Highlighting Top 30% Values:
In this example, we will learn how we can highlight “Top 30%” values from selected set of values with this example.
We will use same 50 values which we have used above. Select all 50 values and select “Top 10%” option from “Conditional Formatting” as shown in below image.
“Top 10%” dialog box will appear on screen. We have to provide 02 inputs in this dialog box:
1st Input: % value which we wish to highlight (“30 ” to highlight “Top 30% values”)
2nd Input: Formatting Style in which we wish to highlight “Top 10%” values. (In this case we have selected formatting style as “Green Fill with Dark Green Text”.
Once we hit “OK” button, we will see Top 15 values will get highlighted from selected cells in selected formatting style (30% of total 50 values is “15”).
Use this option in your daily work and make your data visualisation much beautiful and attractive.
Excellent Sir
ReplyDelete