Filters are an ACE function you can apply to a spreadsheet to temporarily hide the data you don’t want to see, so you can drill down and focus on exactly what matters. The absolute beauty of them is that they’re temporary, your data goes right back to its original state the moment you switch them off.
Turning filters on
Highlight your header row, go to the Data ribbon, and select Filter. A small arrow appears at the top of every column, and clicking it shows every unique value in that column. Untick Select All, then tick just the items you want, for example a single Sales Region or Product Type, and the rest disappear from view.
There are 4 ways to tell a filter has been applied: the arrow changes to a funnel icon, the row numbers skip wherever rows are hidden, the row numbers themselves turn blue, and at the bottom of the sheet you’ll see something like “7 of 20 records found”, an instant count with no formulas needed.
Filtering by more than one column
You’re not limited to filtering a single column, you can apply filters to several at once and they’ll work together. If you’ve got lots of unique items to choose from, the Search box inside the filter list is much quicker than ticking one by one, just type part of what you’re after and the list narrows automatically.
Switching filters off
Click back onto the funnel icon at the top of the column and tick Select All, and your view returns to normal. Bonus Tip: the Data ribbon also has a Clear button right next to the filter icon, which removes filters from ALL columns in one go.
Advanced Filter, for the trickier jobs
If you need something more sophisticated, like copying filtered results to a different sheet or pulling unique records only, Advanced Filter (also on the Data ribbon) lets you set your criteria in separate cells first. It’s not used nearly as often as AutoFilter, but it does still have its place.
A few things to watch for
- The filter dropdown can only display 10,000 unique items, though the search box still works beyond that.
- Mixed data types in one column (text and numbers together) can stop filters working correctly.
- Rows hidden manually before filtering may stay hidden afterwards too.
- If new rows or columns are added to your data, the filter range doesn’t always extend automatically, so check it now and then.
AutoFilter really is one of Excel’s most ACE tools, simple, flexible, and genuinely one of the fastest ways to make sense of a spreadsheet that’s grown too big to read at a glance.
