How to filter with multiple criteria

In this lesson we'll show you how to filter data using more than one column.

Let's take a look.

Here we have a table that contains 300 property listings. Each entry has information for price, address, property type, bedrooms, baths, square footage, cost per square foot, year built, and date listed.

Let's use Excel's filter tools to explore this information.

First, we need to enable filter mode. Select any cell in the table, then click the Filter button on the Data tab of the ribbon. You can also use the keyboard shortcut Control-Shift-L to toggle filter mode on and off.

Let's start by filtering to show only properties below $500,000. First, click the arrow in the Price column to display filter settings. Then, choose Number Filters and select "Less Than...". Enter 500000 in the dialog and click OK to apply.

The table is now filtered, and the status bar tells us how many records are displayed.

Now let's filter the list to show only single family homes. Click Select All to deselect all types, then click Single family. There are now 135 properties visible.

Let's further restrict listings to show only properties with 4 bedrooms or more with at least 2 baths. Both of these filters use Greater Than Or Equal To, in the Number filters menu.

Finally, let's filter to show only properties listed in 2013. The Date Listed column holds a date, so we see a Date Filters item in the filter settings. The Date Filters menu contains a huge list of options for relative time periods. But we can also use the Date tree below to select all dates in 2013.

Now, based on the five filters we've set, there are 11 out of 300 properties displayed. You can hover over any filter button to see a description of the applied filter.

You can clear filters one at a time by selecting Clear Filter in each column. You can also use the Clear button in the ribbon to clear all filters at once.

Let's undo and look at one more way to clear all filters at the same time. When you'd like to reset a table with multiple filters in place, you can just click the Filter button on the menu. This clears all filters and disables filter mode altogether.