applied tips : march 2009

microsoft excel : filter long lists quickly

AutoFilterWhen working with large amounts of data, it is often useful to narrow down the data for review.  While Pivot Tables offer far more flexibility for overall analysis, a quick way to sift through data is the AutoFilter feature of Excel 2003 and 2007.

Setting a List up for AutoFilter

The AutoFilter feature will work with nearly any list of data in Excel, but it's helpful to set a few ground rules first:

  1. The first row (1) of your worksheet should be headings for each column.
  2. There should not be any gap between your column headings and the data underneath them, so eliminate any empty rows that might be there.
  3. Don't allow any blank rows with your list, remove those too.
  4. Be consistent with your spelling and labels... for example, don't write "Sacramento" for some records and "Sac" or "Sacto" for others.
  5. Enter dates as full dates, with month, day, and year so that Excel can understand them.

Activating AutoFilter

Sort and Filter MenuStart by selecting any single cell in your table of data. It doesn't matter which row or column you select, just don't select a full range of cells.

In Excel 2003, pull down the Data menu and choose Filter, then AutoFilter

In Excel 2007, switch to the Home tab and pull down the "Sort & Filter" menu, then choose Filter.

Once activated, you will notice small down-arrow icons located next to each column in your table. 

Using the AutoFilter

Using the AutoFilterIn Excel 2003, you can filter the data by any column by pulling down an arrow and choosing any one of the values displayed.  (Excel automatically displays each possible value from the column.)

In Excel 2007, you've got more options. You can use the check boxes to show or hide any combination of values from the menu. Toggle the "Select All" checkbox to quickly show or hide every option in the list, then mix and match to your liking.

Depending on your version of Excel, you can also choose from a variety of special options:

Need More Filtering?

For much more complex filtering, consider using the Advanced Filter or Pivot Tables.

Showing all Records

If you later decide to show all available records, you can quickly disable the effects of the AutoFilter.

In Excel 2003, return to the Data menu and choose Filter, then select AutoFilter again. This turns it off.

In Excel 2007, return to the Home tab and pull down the "Sort & Filter" menu again, then either choose Filter (again) to turn the feature off or Clear to reset all columns to their original unfiltered status.

Quick Notes about AutoFilters

When you save a workbook with AutoFilters turned on, your filter is saved too. This is great if you want to show someone a specific set of data, but don't forget that they'll be able to disable the filter (if they know how). Don't rely on this for hiding sensitive data.

Setting an AutoFilter also affects what is printed. This can be a great way to print just the data you want, and save lots of paper in the process.

You'll be able to tell if a column is filtered by looking closely at the filter icon at the top row of each column. In Excel 2003, filtered columns have a blue arrow. In Excel 2007, a filter icon appears instead  of a pull-down arrow.


upcoming classes on excel

Classes on Microsoft Excel are scheduled at University of the Pacific this March, and you can attend!  Review the Upcoming Classes for more information.

schedule a class on excel

Request an on-site Excel class from Applied Office. Sessions are inexpensive and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time.  Learn more here