Using the AutoFilter: A useful if slightly twitchy tool

Sooner or later, you're going to get a huge spreadsheet with way too much data to scan visually.

How do you, ahem, filter out what you don't want to see?

One way is to use the AutoFilter.

AutoFilter Basics

Let's say you've got this spreadsheet of employees.

You'd like to just take a look at those from Montana, or those with a particular number of years of service. Something like that.

Click somewhere in the data, and choose Data > Filter > AutoFilter.

You see arrows by all the headings.

Click on one  of the arrows, and choose to view all records containing one of the values, or all records containing the top 10, i.e. the ten most frequently occurring values in that column.

Here are the results for selecting one value for one column.

If you choose another value in another column, then you get rows that have the selected value for BOTH columns.

Here, I get rows for people who are in Montana, AND in Kalispell. Which works out fine since Kalispell is a city in Montana.

 

However, if I choose to view records for people from Montana, and from Portland (a city in Maine and in Oregon but NOT in Montana), I get nothing.

To go back to viewing all the values, select All from the list.

Then you get to view all the records again, once you've selected All for any columns you restricted.

When you're done and want to get rid of the little arrows, click somewhere in the data, and choose Data > Filter > AutoFilter again. There'll be a checkmark and when you select AutoFilter, it will go away.

There's the data the way it was before you started.

Issues With AutoFilter

Here's where things get a little twitchy. What if you try to turn off the AutoFilter and you have not selected a cell somewhere within the AutoFilter results?

You get this.

When you get this, click OK and click somewhere in the data.

Then choose Data > Filter > AutoFilter again. You won't see the checkmark, but that's OK.

Then choose Data > Filter > AutoFilter yet again. This time you'll see the checkmark.

And then the arrows will disappear and you're back to normal.

I also recommend liberal use of the Undo feature, Ctrl Z or click the Undo icon. You can undo at least 20 and possibly more depending on how your system is set up.

Can You Delete Rows When in the AutoFilter Without Deleting the Intervening Data?
I'm glad you asked. Yes, you can. Here's a demo.  Look at the range from row 15, Dan Montbatten, to row 20, Beth Jerlin. They're both from Montana.  In between you've got Jon, Marcus, and Kyle.

I'm going to view only people from Montana, which includes Dan and Beth but excludes the three rows between.

Now I'm going to delete Dan and Beth.

And they go away. However, Jon, Marcus, and Kyle are still there.

 

---

---

Comments

[hawkse]

Another very useful feature is to use autofilters to sum up data for only the visible data.

A slight gotcha there is that one has to use the SUBTOTAL function as opposed to the SUM function.

[Solveig]

Hi Hawkse,

Good idea. However, I just tried it with SUM and it works fine; it only sums what is visible.

You bring up another point that I've been thinking of, though, which is that the AutoPilot feature combines filtering and calculations. I've done one entry for it and I think I'll be doing another.

Solveig

[Huw]

Operations which do NOT affect filtered out rows:
Copy
Delete contents
Delete row
Format
Find & Replace in current selection

Operations which DO affect filtered out rows:
Fill
Cut
Paste
Move

For way too much information (I hesitate to say clarification) on this extremely unintuitive behaviour, see:
http://www.openoffice.org/issues/show_bug.cgi?id=33851
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851
http://wiki.services.openoffice.org/wiki/Talk:Calc/Drafts/Issue_33851

[Solveig]

Huw,

Thank you! It looks pretty inconsistent, doesn't it? Or as you say, not intuitive.

Thanks for the info, this will be very useful.

Solveig

[Meera]

Regarding AutoFilter, I have worked exactly it is mentioned above. I have calc files with 1000 rows and when I use Autofilters it displays the matched rows but below that, it also displays unmatched rows, for which I have not understood the reason. Kindly If you can suggest.

[Carole]

How do you apply auto filters on multiple sheets in a spreadsheet? If I set an auto filter on multiple columns on sheet 1 then go to sheet 2 and try to set auto filters on some of the columns on that sheet the auto filters on sheet 1 disappear.