Excel makes it incredibly easy to filter your data. Open up the example workbook and you’ll see what I mean.
On the first page, you’ll see that we have a list of car makes, models, model years, and values. We can use the AutoFilter to get all sorts of information from these columns.
To enable the AutoFilter, click on any cell in the table, and click the Filter button in the Data tab of the Ribbon. You’ll see some arrows appear in your header row:
Those little arrows might not seem like much, but they open up a lot of power.
Click on the filter arrow in the Make column, and you’ll see lots of options:
At the top, you’ll see buttons for sorting your data alphabetically (or in reverse) by the selected column.
There’s also Sort by Color, which we covered in our article on sorting, and Text Filters, which we’ll get to in a moment.
For now, though, look at the list at the bottom of the pop-up menu. You’ll see a list of the different car makes in the list, each with a checkbox next to its name.
Unchecking one of those boxes filters out that particular make from the list. Try unchecking “Honda,” and you’ll see that the first entry in the list disappears:
Go back in, click on that same box to add a checkmark, and you’ll see that the first entry comes back.
Now, try removing the check from the “(Select All)” box.
This removes the check from every entry in the list. Excel won’t let you click OK if you have a completely empty list, though.
So check the box for BMW and click OK.
Click Clear Filter From “Make” in the menu, and you’ll have every one of the original entries back.
Take note of the search box directly above the list, as well.
If you start typing the value you’re looking for, Excel will show you the matching values: