How to use Table filters

In this video, we'll look at how filters work in an Excel Table.

When you first create an Excel Table, you'll see filter buttons automatically added to the top of each column.

Each filter is customized to match the contents of the column.

For example, when I click the filter for Color, Excel displays a checkbox for each color that appears in the data.

This list is dynamic. If I change Purple to Yellow in a row, Yellow appears automatically in the filter menu.

I'll undo that change.

To filter on a single value, click Select All, then check the value.

Notice a filtered column will display a different icon than an unfiltered column.

To clear a filter, use the Clear option in the menu.

When you're filtering data in a table, Excel will display a message in the status bar at the lower left. This message will show how many records are currently visible.

Each filter also contains a search box that performs a "contains type" search.

For example, if I type the letter "n" I'll get colors that all contain at least one "n".

Directly above the search box is a menu with commands linked to column contents.

Since colors are text values, the options here are for text.

So for example, I can filter on colors that begin with the letter "p" which in this case shows all records with a color of Purple or Pink. 

In the Quantity column, this menu shows options for numbers, since quantity is numeric.

I'll filter on records that have a quantity of greater than 17.

Filters are additive.

Each time you add a new filter, you'll further restrict the number of records you see.

Here we are looking at records where the color is Pink or Purple and the Quantity is greater than 17.

When I filter on the year 2016, we get an even smaller list of matching records.

Dates are automatically grouped by year and month.

In addition, dates will have a long list of options related to the current date.

You'll see commands to filter on tomorrow, today, yesterday, next week, this week, and so on.

Once you have a number of columns filtered in a table, a quick way to reset the filter and show all records is to turn the filter off and then on again with the keyboard shortcut Control-Shift-L.

When you toggle the filter controls this way, all filters are cleared and all rows are displayed.

If for some reason you want to hide filter controls, but leave active filters in place, you can use the Filter checkbox under Table Tools.

This setting only toggles the visibility of filter controls.