Small Business Center Home > How to...

How to Use Excel Filtering to Find Data Fast

Many of us find Microsoft Office Excel spreadsheets very useful for compiling information about customers, products, sales revenues and other types of data. But when the volume of data in a single worksheet grows to fill dozens of columns or rows, sorting through it can be a challenge. If you want to isolate, for example, your top 10 customers in a particular region over the last six months, you might spend a long time reviewing your data entries.

Fortunately, Excel includes an easy-to-use AutoFilter to show just what you want to see and hide the rest. Filtering doesn't change your data in any way. As soon as you remove the filter, all your data reappears, exactly the same as it was before.

Here's how to use the AutoFilter tool in Excel.

On This Page
1. Make Sure the Data Type Is the Same in Each Column1. Make Sure the Data Type Is the Same in Each Column
2. Activate the AutoFilter2. Activate the AutoFilter
3. Start Filtering Data3. Start Filtering Data
4. Apply Additional Filters4. Apply Additional Filters
5. Use Advanced Filtering Techniques5. Use Advanced Filtering Techniques
6. Turn Off Filtering6. Turn Off Filtering

1. Make Sure the Data Type Is the Same in Each Column

In your worksheet, the top row of each column should have a heading that describes the contents of the column, such as "Product Number" or "Customer."

Excel_AutoFilter_1createheadings

The data in each column should all be the same type. For instance, do not mix text in a column with numbers, or numbers in a column with dates.

2. Activate the AutoFilter

Now click inside any data cell and activate the AutoFilter by doing the following:

•

In Office Excel 2003, click the Data menu, point to Filter, and then click AutoFilter.

•

In Office Excel 2007, click the Data tab and in the Sort & Filter area click Filter.

The AutoFilter arrows now appear to the right of each column heading.

Excel_AutoFilter_2activate

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.

3. Start Filtering Data

Suppose your worksheet contains customer sales data. Each customer entry includes information about the customer's location, products they purchase, purchase dates, and revenues and profits from each purchase. Perhaps you want to view sales activity only for those customers located in the West region. Excel can help you do this.

To view only the sales activity from customers in the West region, you click the AutoFilter arrow in the column with the Region heading. When you click an AutoFilter arrow, a list is displayed. The list contains each of the items in the column, in alphabetical or numeric order, so that you can quickly find the item you want. In this instance, you scroll to West, and click it.

Excel_AutoFilter_3start-filtering

When you click West, Excel hides all the rows on the worksheet except for those that contain that text in this column.

Excel_AutoFilter_4hiderows.png

4. Apply Additional Filters

If you want to focus on even more specific information, you can filter again on another column, and then again on another column, and so on. You can click the arrow next to any heading in any column to apply a filter.

After filtering by Region, for example, you can click the AutoFilter arrow on the Product Number column and filter that column to see only the West region customers who purchased product number 12-100.

Excel_AutoFilter_5add-filters

You can filter columns in any order you choose. The filters are applied progressively, in the order you apply them. Each filter limits the data to which you can apply the next filter.

5. Use Advanced Filtering Techniques

Excel also enables you to perform more intricate types of filtering. Two particularly useful types are the Top 10 filter and custom filtering.

Finding the Top (or Bottom) 10 in a column
You can use the Top 10 filter on columns of numbers or dates. With Top 10 you can find either the top items or the bottom items (the smallest or largest numbers or dates). And you're not limited to finding the top 10 items or the bottom 10 items. You can choose how many items you want to see: only 1 or as many as 500. You can use Top 10 to find the highest-priced or lowest-priced products, to identify employees with the most recent hire dates, or to see the top or bottom student grades.

To use Top 10 on a column of data in Excel 2003, click on a data cell in the column and then click the column's AutoFilter arrow.

•

In Excel 2003, click (Top 10…) near the top of the drop down list.

•

In Excel 2007, click Numbered Filters and then select Top 10.

The Top 10 AutoFilter dialog box opens. In the dialog box, select either Top or Bottom. Then select a number. Finally, select either Items or Percent.

You can filter columns in any order you choose. The filters are applied progressively, in the order you apply them. Each filter limits the data to which you can apply the next filter.

Using custom filters
When you filter by choosing from the AutoFilter drop-down list, you hide everything except your single choice. If you want to see more than one selection in a column, you can create custom filters.

To create a custom filter,

•

In Excel 2003, click (Custom…) near the top of the drop down list.

•

In Excel 2007, click Numbered Filters and then click Custom Filter.

The Custom AutoFilter dialog box opens. You can now enter two filtering requirements for the column of data. For example, you could see customers who purchased product numbers 12-100 and 12-500.
Note: Be sure to select the Or button or you will see no results at all.

Excel_AutoFilter -6custom-filter

6. Turn Off Filtering

How you 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.

•

To remove the filters from your spreadsheet, simply deselect AutoFilter (Excel 2003) or Filter (Excel 2007) on the Excel menu. All the data in the spreadsheet is revealed again.

Related Articles

•

Track Finances with Excel

•

Use KPIs to Improve Profitability

•

Use Excel Template to Help Capture New Business

Product Information

•

Office Small Business 2007

•

Office Professional 2007


! Quick Tip

If you don't know that a spreadsheet has been filtered, you may wonder why some of the data seems to be missing when you open it. Here's how to tell if a worksheet contains filters.

Check the status bar.
Immediately after data is filtered, you'll see the results of the filter in the lower-left corner of the status bar such as "126 of 2155 records found." After a time, the numbers will disappear, and Filter Mode will appear on the status bar instead.

Look at the row numbers.
You can tell by the discontinuous row numbers that some rows are hidden, and the visible row numbers change colour to indicate that the rows are filtered.