Join Dennis Taylor for an in-depth discussion in this video Using the Top 10 (value or percent) option, part of Excel 2011 for the Mac: Managing and Analyzing Data.
- In this worksheet called Top Ten Filter, we wanna talk about a feature that has the name top ten. It applies only to numeric fields. Let's imagine we're about to use a filter on the Comp field here, column J, to the right. Clicking anywhere within the data. We'll simply go to the data tab in the ribbon, activate the filter over on the left side. As I scroll to the right, click the drop arrow for Comp. Compensation. And under Choose One, we have an option called Top Ten.
Notice also, we have Bottom Ten. Recognize too, Top Ten, once we make the choice, we get some additional choices, including the idea that maybe top ten items isn't necessarily what we mean all the time. Now, there are about 700 or so names in this list. We see top ten in the background there. The top ten salary amounts, compensation amounts. But, we could have chosen top ten percent. And 10% of 740, whatever it is, is likely to be around 75 or so, 74.
So, we could have chosen percent and we can see the entries in the background there. So, at times, we mean items and an actual count. At other times, we mean percent. Now, the number 10 can vary as well. If we only wanted to see the top one percent, we'd get about seven names here. There they are, top one percent. So, we can choose percents or items and we can change these from 10 to any number we wish. The same thing with items too.
We wanna see the top 15 items. There's one for the moment. Let's make it be 15. And there we have the top 15. So, a lot of variability, top ten is the grabber name, you might say. That's the choice. Bottom ten, of course, we could have gone that way as well too with the same set of options there as well. Now, I wanna show you what this does not mean and sometimes, this can be a little bit confusing. What if we were to clear the filter here for column J, so the data has no filter active at the moment. I'm going to filter the list by department and within that, I'm going to unselect all the choices and choose only the manufacturing departments, the largest one and if I click column C and look at the bottom of the screen, if I've activated the count here, there are 151 entries there.
Now, I wanna see the top 10 salaries. We saw that feature before, top 10 compensation amounts, that is. In column J, we'll click the drop arrow there, choose one, top ten. But, we're only seeing three. So, what does top ten mean? Top ten items. Well, out of the entire list of top 10 compensation amounts, only three of them are in the manufacturing department. So, what we can not do is show the top 10 compensation amounts within the manufacturing department.
Now, we could do that indirectly by simply removing the filter from Comp, right here. We've got a list of manufacturing here. We can sort the list in descending order. We'll simply click the drop arrow for Comp here and choose descending and we could say and this is not as fast, perhaps as we might hope it to be, but ya know, we're highlighting the eight or 10 or 12 top ten salaries this way. But we're not using the filter actually to get to them.
So, top ten means within the entire list. So, again, if we're only showing the Manufacturing group and we go to the Comp filter and choose top ten, we're not gonna be getting 10. Only three of our top 10 salaries in the entire list are in the manufacturing group. So, although a valuable feature, it might not always mean what you hope it means. Keep in mind again, we can change items to percent. The 10 could be any number we wish and remember also, that top ten could just as easily be bottom ten, so lots of choices available with the top ten, bottom ten options.
- Multiple-key sorting
- Sorting columns left-to-right
- Filtering columns
- Creating a top-ten list with values or percentages
- Using Advanced Filter for complex filters
- Setting up subtotals
- Using the Remove Duplicates command
- Using SUMIF, COUNTIF, and related functions
- Working with the database functions, such as DSUM and DMAX