Excel Filters 4 Ways

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 😉

Number Filters

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:

Text Filters

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:

1. Data tab > Sort and Filter group > Clear
 

 

2. Right-click > Filter > Clear Filter From…
 

 

3. Click on the Filter button for the column > Clear Filter From …
 

 

4. My favourite; click the Clear Filter icon you’ve added to your QAT now that you love Filters:

Want More?

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.

Happy Filtering.