Filtering Data Quickly with AutoFilter

An important aspect of working with large amounts of data is the ability to zero in on the most important data in a worksheet, whether that data represents the best 10 days of sales in a month or slow-selling product lines you may need to reevaluate. In Microsoft Excel, you have a number of powerful, flexible techniques you can use to limit the data displayed in your worksheet. One of those techniques is to filter the contents of a workbook. Unlike sorting, which arranges worksheet rows according to rules you set, filtering a worksheet hides rows that don’t meet the rules you define.

Create an AutoFilter

Click any cell in the range you want to filter.
Click the Data tab.
Click Filter.
Click the filter arrow for the column by which you want to filter your worksheet.
Select the check boxes next to the values by which you want to filter the list.
Click OK.
Tip
When you create a custom AutoFilter, the menu item changes to reflect the type of data in the column (date, text, number, and so on).
Tip
If you created a data table to store your data, the filter arrows appear on the table’s header row by default.

Display All Rows in a Filtered List

Click the filter arrow of the filtered column.
Click the Select All check box.
Click OK.
Tip
Clearing the Select All check box deselects all of the check boxes in the filter list.

Create a Custom AutoFilter

Click any cell in the list you want to filter.
Click the Data tab.
If necessary, click Filter to display the filter arrows.
Click the filter arrow of the column for which you want to create a custom filter.
Point to Text Filters.
Click Custom Filter.
Click the Comparison Operator down arrow.
Click the comparison you want to use.
Type the value by which you want to compare the values in the selected column.
Click OK.
Tip
When a column is filtered, the filter arrow that appears to the right of the filtered list header is replaced by a filter icon.