MS Excel 2003 Training 101 - All About AutoFilter

Create a filter

Click a cell in the data you want to filter. Then on the Data menu, point to Filter, and then click AutoFilter.

AutoFilter arrows will appear to the right of the column headings on all columns of the data you are filtering.

Note    If you select an entire column instead of a single cell before clicking the AutoFilter command, an AutoFilter arrow will appear only on the selected column, not on all columns of the data.

When you click an AutoFilter arrow, a list is displayed of the items in the column, in alphabetical or numeric order, up to a total of 1,000 items. In the list, click the item you wish to filter on. The rows containing that item will be displayed in the worksheet. All other rows will be temporarily hidden.

After you apply an AutoFilter to one column, you can filter another column, then another column, and so on.

Filtered data is not changed, rearranged, or moved. It's just hidden until you remove the filter.

For best results, worksheet data should be neat and tidy before you filter.

The top row of each column should have a heading that describes the contents of the column, such as "Product Name" or "Employee Name."
The data in each column should all be of one type only. Do not mix text in a column with numbers, or numbers in a column with dates.
Data should not be interrupted by blank rows or blank columns. Individual blank cells are okay.
Data should be on its own worksheet. If that's not possible, it should be separated from other data by a blank row or blank column.

Note    If a column contains blank cells, a Blank or NonBlank option is available in the AutoFilter list for that column. You can use Blank to see all blank cells in the column, and NonBlank to see all rows without blank cells.

See whether filters have been applied
Because AutoFilter temporarily hides data, you might wonder what has happened to the data if you don't know that a filter has been applied. Excel provides several ways to see that data has been filtered.

Immediately after data is filtered, the results of the filter are displayed in the lower-left corner of the status bar in the form "126 of 2155 records found." After a time, Filter Mode will appear on the status bar instead of the numbers.
If you do not see the status bar, on the View menu, click Status Bar.

Row numbers indicate that some rows are hidden. Also, the row numbers change color, typically to blue, to indicate that the rows are filtered.
The AutoFilter arrow in any filtered column also changes color to indicate that the column is filtered.

Note    If you do not see the results of the filter in the status bar, and the status bar is turned on, the worksheet may contain more than 51 formulas.

If you want to apply a filter when the worksheet contains more than 51 formulas, you can temporarily prevent formulas from calculating while you use AutoFilter. On the Tools menu, click Options. Then click Calculation, and click Manual.

Important    When you are through filtering, be sure to follow these steps again, but select Automatic instead of Manual. If you do not set it back, your formula results may contain errors.

Remove filters

How to remove filters depends on how many filters you have applied, and from how many columns you wish to remove filters.

To remove a filter from one column, click the AutoFilter arrow next to that column, and then click All. That command will display the rows hidden by that filter.
To remove all filters at once, point to Filter on the Data menu, and then click Show All. That command will display all rows hidden by all filters on the worksheet but leave AutoFilter turned on.
To turn off AutoFilter, point to Filter on the Data menu, and then click AutoFilter.
Remember, filtered data is not changed, rearranged, or moved, it's just hidden until you remove the filter.

Note    The order in which you remove single filters controls what data appears. If you don't see what you want when you remove filters in a particular order, you can use Show All to remove all of the filters, and then filter the data again.

Filter for the top or bottom items or percentages
You can use the Top 10 filter on columns with numbers or dates.

1. Click the AutoFilter arrow
in the column that contains the numbers or dates, and click (Top 10…). A dialog box will open.
2. In the box on the left, click Top or Bottom.
3. In the box in the middle, enter a number from 1 to 500.
4. In the box on the right, click Items or Percent.
5. Click OK.

Sort filtered data

Important    Unlike filtering, sorting rearranges data. After you sort data, you must undo the sort to put the data back where it was. If you save a worksheet before undoing the sort, you cannot then undo the sort.

To sort in ascending or descending order
1. Click the AutoFilter arrow of the column you want to sort.
2. Click Sort Ascending or Sort Descending.
3. To undo the sort, click the Undo button on the Standard toolbar, or on the Edit menu, click Undo Sort.

To sort by more than one criterion
1. Click a cell in the data you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by and Then by boxes, click the columns you want to sort, starting with the most important.
4. Select any other sort options you want, and then click OK.
5. Undo the sort, by clicking the Undo button
on the Standard toolbar, or on the Edit menu, by clicking Undo Sort.

Doing calculations with autofiltered data

If you need to do calculations with autofiltered data, do not use the normal Excel functions, because they will use all the data, not just the rows that you can see. Instead, use the SUBTOTAL function.