AutoFilter details

We filtered by unchecking and checking values in the Sort and filter data video.

There are many other powerful ways to filter.

If a column contains blank cells, you can filter for cells that are blank and those that aren’t.

Click any cell in the range or table. On the HOME tab, click Sort & Filter, and click Filter.

Click the down-arrow for the column. This is also referred to as the AutoFilter. Uncheck (Select All) and check (Blank).

(Blank) won’t be an option if there aren’t any blank cells in the column.

Click OK, and only the row with a blank value in the Code column is displayed.

To display cells that aren’t blank, leave all options checked except (Blank).

You can also use criteria to filter. Click the AutoFilter for the column you want to filter.

Since the Code column contains only numbers, we get the Number Filters option. When I point to it, there are a lot of criteria.

Click one, such as Greater Than. Criteria will be different for different types of data, such as text.

Type a value, such as 50000, click OK, and only the rows where the value in the Code column is greater than 50000 are displayed.

You can apply an AutoFilter to multiple different columns.

Click the AutoFilter for a different column, such as Category.

Configure the filter for this column, such as unchecking Drinks. Click OK, and now multiple filters are applied to the data.

Down here, Excel tells us how many records meet all the criteria, out of how many total records there are; in this case, 2 of 9 rows.

There are specific criteria filters for text. There are also specific criteria filters for dates.

Dates has a huge number of options. I click a criterion, such as After.

In the Custom AutoFilter dialog, enter a date, such as 5/1/2013.

Click OK, and only rows where the Last order value is after 5/1/2013 are displayed.

You can also AutoFilter by cell and font color. Click an AutoFilter. Point to Filter by Color.

This won’t be an option if there aren’t multiple cells or font color formats in the column. Click an option.

I want to see the products that have been discontinued, so I click the red cell color.

Only rows, where the cell in the Discontinued column is formatted with a red fill, are displayed.

To remove the filter for a column, click the AutoFilter for the column, and click Clear Filter From.

To turn off AutoFilter completely, click the Sort & Filter button on the HOME tab, and click Filter.

Up next, Advanced filter details.