Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Sometimes, it seems like we are inundated with Top Ten lists: Top Ten movies, Top Ten vacation spots, and so on. Businesses also use Top Ten lists to reward their employees, perhaps giving awards to their top ten sales representatives. In this movie, I will show you how to find the highest and lowest values in your worksheets and change those cell's formatting so that the value stand out. I am going to work with a fairly simple example, just so you can see how these conditional formats work. They are called Top Ten Formats, but you can use any number that you want. So, for example, here I have a data list that has numbers 1 to 10.
If I want to create a conditional format that changes the formatting of the cells that contain the top three values, I can select the cells, and then on the Home tab of the Ribbon click the Conditional Formatting button, point to Top/Bottom Rules, and then click Top 10 items. In the New Formatting Rule dialog, I can select either the top or the bottom values. In this case, I want to find the largest three values. So I will stay with Top, and I can create a rule that finds three.
And again, I can change my formatting by clicking this list box's down arrow, clicking custom format, and I will change the background fill color to blue, and make the font bold, and change the color back to Automatic, which is black. That looks good. My preview looks good. Click OK, and there is my complete format. And when I click OK, Excel changes the formatting of the cells with the highest values. Now, the conditional format is already applied, so, if I were to change the value in cell A3 to 12, and press Return, then Excel removes the formatting from cell A9, which contained the value 8, and instead transferred it to cell A3.
If I were to change it back to its original value of 2, then press Return, then the conditional formatting returns to its original state. You can also create what's called a Percentage Top 10 conditional format. So let's say I will press Command+Z several times to undo my edits and finally the last time I did that, I removed the conditional format. This time I am going to create what's called a Percentage rule. And again, I will go to Conditional Formatting, point to Top/ Bottom Rules, but this time I will click Top 10%.
A Top 10% Rule finds the values that are in the top 10% of a particular range. So let's say, for example, that I wanted to find the top 40%. I will find the top values, and I will change this number to 40, and you will see that the percent check box is selected. I will just stay with this format this time, just to speed up the demonstration process a little bit. And when I click OK, you see that Excel has changed the top four values, which is 4 out of 10, or 40%.
One thing I should point out is that percent refers to the top 40% of values, not values in the top 40% of the range. As an example, let's see what happens when I change the value in cell A11 to 100. If Excel looked at values in the top 40% of the value range, that means it would only format cells that contain the value 61 or higher, because that would be the top 40% of the range from 1 to 100. But instead, when I press Return, you see that we have the top four values in this range, and it doesn't matter that this one is so much larger; it's only the top four, or the top 40%.
So when would you use a Top 10 format versus a Top 10% format? Well, you should use a percentage format when your list of values could grow or shrink. So, for example, if you have your list in an Excel table and you apply the conditional format to the entire column of that Excel table, then that means that the format would be applied to the entire thing, regardless of how many entries were in it. In that case, you might want to use a conditional format that finds a percentage of values. If your list is static, or if you always want to find, say a certain number, such as the top five or the top 10, then you should create a Top 10 filter and not to worry about the percentage.
Finding the highest and lowest values in your worksheets provides valuable information about your business. You should use Top and Bottom 10 conditional formats, in combination with filters, to learn as much as you can about your data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86546 Viewers
80 Video lessons · 135909 Viewers
59 Video lessons · 54659 Viewers
52 Video lessons · 68545 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.