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

I Thought Filters Were For Things Like Fish Tanks and Coffee Makers…

Friday, April 7th, 2006 by | Filed Under: MS Excel, MS Office Help
 
Loading...


I Thought Filters Were For Things Like Fish Tanks and Coffee Makers…

Have you ever found yourself lost while working with a huge list or set of data in MS Excel?

Did you find that you were continually scrolling through data looking for a particular value?

I bet more than once you caught yourself wishing for a way to just see what you want and get rid of the rest.

Well, if this is your wish, you’re in luck because, believe it or not, you can get Excel to do the hunting for you.

What you need to know about is the useful feature, simply named “AutoFilter.”

Excel allows you to “filter” data to find only what meets your current requirements. Basically, with the AutoFilter feature, you get to sort out the unwanted stuff and have Excel only display the “good stuff.”

(Don’t worry, it doesn’t delete the other stuff. It just hides it for awhile).

So, if you’re all for having Excel do the data hunting automatically, then read on as we explore the wonderful world of AutoFilters.

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 three sales amounts from the list, or all customers who live in a certain city, or all sales made by a certain sales rep, etc.

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

Did you see what just happened?

No?

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 list 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, 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 four 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 want, you can print it, copy / paste it, etc. You can do 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, 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 only one or two columns, 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, 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 the data is restored, you’re ready to start filtering for the next item criteria you need.

Whew!

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

P.S. – Some of you may be thinking that you could just sort the data to get what you need. This is true. However, I have found that this is faster and returns the data to its original order when done. On the other hand, sorting requires more sorting or “undoing” to get the original data back.

Leave a Reply


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

I Thought Filters Were For Things Like Fish Tanks and Coffee Makers?

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


I Thought Filters Were For Things Like Fish Tanks and Coffee Makers?

Have you ever found yourself lost while working with a huge list or set of data in MS Excel?

Did you find that you were continually scrolling through data looking for a particular value?

I bet more than once you caught yourself wishing for a way to just see what you want and get rid of the rest.

Well, if this is your wish then you’re in luck because, believe it or not, you can get Excel to do the hunting for you.

What you need to know about is the useful feature simply named “AutoFilter”.

Excel allows you to “filter” data to find only what meets your current requirements. Basically, with the AutoFilter feature you get to sort out the unwanted stuff and have Excel only display the “good stuff”.

(Don’t worry—it doesn’t delete the other stuff, it just hides it for a while.)

So, if you’re all for having Excel do the data hunting automatically then read on as we explore the wonderful world of AutoFilters.

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:

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: