applied tips : march 2009
microsoft excel : filter long lists quickly
When 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:
- The first row (1) of your worksheet should be headings for each column.
- There should not be any gap between your column headings and the data underneath them, so eliminate any empty rows that might be there.
- Don't allow any blank rows with your list, remove those too.
- Be consistent with your spelling and labels... for example, don't write "Sacramento" for some records and "Sac" or "Sacto" for others.
- Enter dates as full dates, with month, day, and year so that Excel can understand them.
Start 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
In 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:
- Choosing "Top 10" will ask you if you want the top or bottom results, the number of records to display, and whether you want a specific number or a percentage. For example, you can request the bottom 25%.
- "Custom" allows you to create inequality results such as >1000 or "Does not Contain CA".
- You'll find that in Excel 2007, the column is "aware" of what kind of data you have stored in it, and will display options that pertain specifically to those kind of values. When in a date field, as shown in the screen capture to the right, you can quickly filter based on month or year, today or yesterday, etc.
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