Filters are one of my most used Excel tools. They’re not fancy, but they sure do earn their keep.
I’ve even bestowed them with some prime real estate in my QAT:
Filters allow you to quickly hide rows containing data that don’t meet your criteria. You can also apply multiple filters to one or more columns.
1: Filter by Selected Cell’s Value
I use this every day. Usually you’re in the depths of a table of data and you find something you’re interested in and think; I’d like to see more records with this same value.
Easy: right-click > Filter > Filter by Selected Cell’s Value
Now I’ve got myself a list of all the Pavlova data I can eat:
Tip: Notice when you right-click > Filter that Filter by cell’s Value has siblings; filter by cell’s colour, cell’s font colour and cell’s icon. The last 3 are particularly handy for filtering tables with conditional formatting applied.
2: Text/Number Filters
This is kind of 2 uses in one but that’s ok. I like to over deliver 😉
If you click on the Filter button for a column containing numeric values you get the ‘Number Filters’ option where you can wield your wand and filter your data on all sorts of criteria, even Top 10, Above/Below Average as you can see in the list below:
Clicking on the Filter button for a column containing text will reveal the Text Filter options.
You can even create a custom filter and use wildcards in your Filter criteria:
3: Search Box
When you’ve got a long list of items it can be quicker to type some criteria in the search box to reduce the size of the list:
4: Unfilter - 4 ways
Of course once you’ve applied a filter you’ll probably want to remove it. To do so you can either:
- Data tab > Sort and Filter group > Clear
- Right-click > Filter > Clear Filter From…
- Click on the Filter button for the column > Clear Filter From …
- My favourite; click the Clear Filter icon you’ve added to your QAT now that you love Filters:
Why not try Advanced Filters, they’re fun like Filters, but they can do some extra things regular Filters can’t.
And if you have Excel 2013 you can use Slicers to apply your filters to Excel Tables.
If you put “AutoFilter” instead of “Filter” on your QAT, you may first select the cell value and then click directly on the “AutoFilter” to filter by cell value. Quick handy indeed.
However, it works for range only, not for Table… 🙁
Thanks for the tip, MF 🙂