MS Excel AutoFilter Part 2 – Top 10 and Custom Filters

As promised on Tuesday, we will finish our discussion of Excel’s AutoFilter function. Specifically, we need to cover two items in the AutoFilter pull down lists: “Top 10” and “Custom.”

So, let’s get right to it.

To begin, I think we should discuss Top 10.

This is pretty much what the name implies. It’s a listing of the top values or percentage of the list.

Let’s investigate further.

After you’ve opened a file and turned on the AutoFilter (Data menu, Filters submenu, AutoFilter choice), pull down one of the AutoFilter lists for a column.

Now select (Top 10).

This window should open.

You see, this choice doesn’t have to give just the top 10 items. You do have some choices here. There are different ways to customize what and how many it’s filtering for.

The first field allows you to choose to filter for either the “Top” or the “Bottom” of the list.

In the middle, you can select how many items you want listed or the percentage of the list you’re looking for Excel to display.

The third choice is what you want displayed. If you choose “Items,” you’re flat out asking for a list of however many items from either the top or the bottom. It doesn’t matter how big or small the original list is. If you ask for the top two, you get only two items in the filtered list.

The other option here is “Percent.” If you choose percent, you’re asking for the filter to display a percentage of the list (top or bottom values). The bigger the list or percentage number, the bigger the displayed filtered list will be. (For example, 10 percent of 100 items will be a list of 10 items, but 10 percent of 1,000 items will be a list of 100 items. You get the idea).

When you’ve made all your choices, click OK and you’ll be returned to see your filtered data.

If you change your mind, you can go back to the Top 10 window and change the options. AutoFilter will update the displayed list to reflect your new choices.

That one is pretty straightforward, so I’m sure we’re ready to move on to the (Custom) option in the list.

When Custom is chosen from an AutoFilter list, the following window will open.

This window allows for you to enter logical arguments to filter for ranges of values or two exact values.

The pull down lists on the left list the options for the arguments, such as equals, does not equal, is greater than, is less than, contains, does not contain, etc.

On the right, you can either select an item from the pull down list or type in a value of your own.

Between both of these lines, you have a choice of “And” and “Or.”

Basically, you need to decide if the data you need must meet both requirements (the “And” choice) or just one of them (the “Or” choice).

For example, if I wanted to filter my lists for sales totals greater than $85,000 or less than $65,000, the window would look like this.

It’s just a matter of knowing what you’re looking for and then setting the correct ranges to get the job done.

Some of you may have noticed the information at the bottom of the Custom AutoFilter window regarding the symbols ? and *.

These are used as wildcards to get Excel to make approximate matches. For example, if I was looking for all customers with the last name beginning with a C, I would choose “Begins with” on the left and enter C* on the right. (The * simply lets the program know that any letters there will work. It’s the C that it must match).

No matter what you’re trying to get with the Custom AutoFilter, when you’re done setting the criteria, click OK.

Again, you’re returned to the worksheet with only the data meeting your requirements displayed.

No more long searches or complicated sorting. AutoFilter to the rescue!

~ April