Excel Filters! Oh yeah!

Happy Tech Tip Tuesday! Sorry for the long delay between tech tips, it’s been busy times! This tech tip is all about filtering in Excel. Filtering is fun and can be super useful! I thought it would be good to share a few useful tips regarding filtering. There are many more filtering tricks out there, but here are a few that are top of my mind.

Are you filtering anything currently?

One easy way you can tell if any filters are actively filtering out rows is by looking at the color of the row numbers. If the row numbers are black, the filter is not active. If the row numbers are blue, the filter is active.

Nothing is filtered:

Filtering is on: the filter is indicated in the dropdown arrow, and the row numbers have turned blue

Clear that filter!

If you want to easily clear all filters, you can hit ALT then D F S (or you can go to the Data ribbon, and in the Sort & Filter section click “Clear”

Refresh the filter

If you need to refresh the filters, hold CTRL+ALT+L

Filter by Color

Filtering by color can be super useful for finding things that you’ve identified either manually by highlighting, or through using conditional formatting. It’s fairly simple – just click the drop-down arrow, select “Filter by Color”, and select either the font color or highlight color to filter on. Note you can also sort by color.

Using the “Add current selection to filter”

What this allows you to do is add additional items to the list of items. This means that your filter becomes LESS RESTRICTIVE than whatever it was previously filtering for. For example – if you were previously filtering for all words that start with “A”, and you want to also filter for things that start with “B”, you would want to search for “B*” in your filter, then check the “Add current selection to filter” box, and then click OK.

Wild Card!

The question mark and asterisk are very useful when filtering for specific things.

a.       The asterisk ‘*’ represents a wild card for the characters before, between, or after other non wild-card characters. And it can represent an unlimited number of characters. For example, filtering for “A*” will filter for anything that starts with ‘A’ and ends with anything. Filtering for “ap*nges” will result in anything that starts with ‘ap’ and ends with ‘nges’.

b.      The question mark ‘?’ represents a wild card for one character only. For example, if you were looking for all records that have seven letters in them, you would search for ‘???????’.

Here’s the list we are starting with.

When you click the drop-down arrow next to ‘Item’, you would see this:

If you type in “A*” into the search box, you see all results that start with “A”:

And if you click OK you will result in filtering for just those two selections.

If you type “Ap*nges”, you will find:

If you search for “???????” you will get:

Happy filtering!!!

Cheers,

Daniel