Excel has a right click Filter option that speeds up filtering by a single value. You can hack that shortcut to do a little bit more.

Hat tip to Mr Excel (Bill Jelen) for this hack idea.

Below is a list I use in training when demonstrating filtering and sorting.

It has 1000 rows of data and a number of different column types.

You can right click on a cell and choose Filter, then Filter by Select Cells’ Value to perform a single item filter.

This is great if the item you want to filter by is in a cell.

If you are more a keyboard orientated person you can select the cell and then press Shift + F10, then press E, then press V to do the same as the mouse shortcut.

Let’s say you wanted to filter all the states that contain an A. So you only want to see WA, SA and TAS.

Normally you have to use a Custom Filter to do that, but you can hack the above shortcut. Check out the image below.

In cell H1002 (the next blank cell in column H) I have entered.

*a*

This uses the wildcard symbol * which represents any number of characters. So it is essentially saying “show all entries with an a”.

I can right click cell H1002 and use the Filter by Selected Cell’s Value and perform a custom filter – see image below after doing it.

You can also do this with values, but the downside is the cell you use to filter will most likely be filtered out.

In the image below, in cell K1002 I have entered.

>50000

I can use the Filter by Selected Cell’s Value on that cell to see all the rows with a salary above 50000 – see image below.

The problem is row 1002 is now filtered out.

It an interesting little hack – have fun using it.