MS Office 101

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.

Office Tip of the Day

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?

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

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

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