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

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 AutoFilters Part 1—The Basics

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

MS Excel AutoFilters Part 1—The Basics

Here’s the scenario: you’re working with a spreadsheet and you’re patiently trying to find certain values—maybe you’re looking for the top 3 sales amounts from the list, or all customers who live in a certain city, or all sales made by a certain sales rep.

Whatever it is you’re looking for, you need to know it now—not an hour from now after you’ve sorted through all the data by hand. And, to make things worse, you’re constantly hoping you weren’t accidentally overlooking something.

So, the question comes to mind: “How can I filter out all the stuff that doesn’t matter right now and have Excel display only the items I’m looking for?”

The answer comes in the form of Excel’s AutoFilter feature.

Now that we can see a few uses for the AutoFilter feature let’s look at the how-to.

As with any other thing we do, you have to have the file with the data to be filtered open.

Select a cell in the data.

Next go to the Data menu, Filter submenu, AutoFilter choice. (Alt then D then F then F will do this too.)

Did you see what just happened?


Take a look again. See the little down arrows next to the column titles?


Yep—that’s the difference.

It’s also the AutoFilter hard at work.

Click on one of the arrows to pull it down.

You should get a menu that looks something like this.


(Your actual list will differ because it contains the data in your column—which is different from mine.)

Basically, Excel creates a list of all possible choices in that column, so if you’re looking for a particular number or name or city, pull down that column’s arrow and choose it from the list.

Instantly you’ll be returned to the Excel worksheet—except it’s different. The only things listed are the ones that meet your chosen criteria.

For example, my table contained monthly data including the number of days in the month. If I use the AutoFilter menu shown above and choose 30 days the table is shortened to display only the 4 months that contain 30 days.

If you need to further filter (using secondary criteria) you can choose another column and pick from what’s available there. (You might want to note that you’ll only see a list of what’s left after the first filtering. Basically you’re filtering the filtered data, not the list from the original data.)

You can continue to filter this way until you get to just the data you’re looking for.

Once you’ve gotten just what you’re looking for you can print it, copy / paste it, etc… whatever you need to do with the data. The point is that you’ve gotten exactly what you needed much quicker than you would if you go through it all by hand.

When you’re ready to show all the data again you have a couple of choices:

If you’re done with the AutoFilter completely then you could go back to the Data menu, Filter submenu, AutoFilter choice to turn it off. This will restore your data to its original state.

Or, if you have other filtering to do on the original set of data, you can tell Excel to Show All. Basically, this returns the data without turning the AutoFilter function off.

(All) is a part of the filter lists on the column titles—if you filtered on only one or two columns then it’s pretty easy to go back to both columns and choose (All). The catch to this method is that you have to do this to all columns used in the filtering process.

If you happened to use a lot of columns for filtering then you may want to go to the Data menu, Filters submenu, Show All choice. That puts it all back no matter how many levels of filtering you completed.

Once data is restored you’re ready to start filtering for the next item criteria you need.


That’s a lot to explore and figure out, especially if you’re someone who has never even heard of AutoFilter before.

I’m sure by now you’ve noticed the other choices at the top of the AutoFilter list (Top 10 and Custom). We’ll discuss those in the next issue, giving everyone a chance to experiment with today’s information before we move on.

So… until then, take a few moments to add filter to your Excel vocabulary and feel the searching power of the AutoFilter function.

~ April

Leave a Reply

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:


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.


Love Worldstart? Refer A Friend!

WorldStart's Premium Membership

Tip Archive