Tech Tips Home
The Best Tech Tips And Daily Deals
Newsletter On The Internet!

Shop online 24hrs a day or call us Mon-Fri
8:30AM-4:30PM EST - 1-800-915-2088
WorldStart Tech Tip And Store Search
Email: Password: Login Remember Me

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

MS Excel AutoFilter Part 2—Top 10 and Custom Filters

Friday, November 19th, 2004 by | Filed Under: MS Excel
 
Loading...


MS Excel AutoFilter Part 2—Top 10 and Custom Filters

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.

image

You see, this choice doesn’t have to give just the top 10 items. You do have some choices here—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” then you’re flat out asking for a list of however many items from either the top or bottom. It doesn’t matter how big or small the original list is, if you ask for the top 2, you get only 2 items in the filtered list.

The other option here is “Percent”. If you choose percent then 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% of 100 items will be a list of 10 items – but 10% of 1000 items will be 100 items long. 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 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.

image

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…

Then 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.

image

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

Comments are closed.

Like these tips? Get them for FREE in your email!

WorldStart's Tech Tips Newsletter

  • Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

  • Tech Tips Weekly - If you don't want our Tech Tips newsletter every day, then sign up for this weekly newsletter to get the best information of the week. Sent on Fridays.

Other Newsletters

  • WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

  • Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.


Enter Email Address:

Subscribe

Your e-mail address is safe with us!
We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Free Newsletter Signup



Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.


Subscribe


Love Worldstart? Refer A Friend!

WorldStart's Premium Membership

Tip Archive


Categories:
Archives: