You're getting ready to go home on a Thursday night when your phone rings. It's your manager, calling about the latest sales report. He has a 7:00 A.M. meeting the next day about the report, and he wants you to analyze the data. As you're talking to him, a spreadsheet attachment shows up in your Microsoft Office Outlook® Inbox. You tell your manager that he can count on you to help him out. You've done this before; you know what he wants.

You open the spreadsheet to see that it's 948 rows long and 6 columns wide. In the past, it would have taken you hours to analyze the report. But now — using the nifty Filter command in Microsoft Office Excel 2003 — you can complete the task in minutes.

Filtering gives you an easy way to pare down your data into smaller chunks. Unlike sorting, filtering does not rearrange your data; it temporarily hides rows you do not want displayed. In this article, you'll look at one of the commands that Excel provides for filtering your sales data: AutoFilter.

Note  If you want to follow the steps in this article, download and use the Product Sales Sample worksheet template.

A sea of data

When you first glance at the worksheet, it's difficult to see trends in the data:

You need to start filtering through the numbers. Here's how.

Turn on AutoFilter

1. Click a cell in the range you want to filter.
2. On the Data menu, point to Filter, and then click AutoFilter.

Drop-down arrows now appear in the column headings in your worksheet.

Look at all sales for a specific product

You remember that your manager always asks you about Chang product sales, because his brother-in-law owns the company that manufactures it. You can quickly look at the Chang sales numbers:

1. In the Product column, click the arrow.
2. On the list, click Chang.

You now have a filtered worksheet of all the product sales for Chang. Note that the other data in the worksheet isn't visible. It's still there, but it's filtered out.

Filtered product sales worksheet

Important  After you've filtered the data, you're only working with a subset of the original, full worksheet. Before you begin working on another filter, you need to return to the full worksheet.

To return to the full worksheet:

1. In the Product column, click the arrow.
2. On the list, click (All).

Find the top 10 sales for Quarter 4

Next, you want to see the top 10 sales for Quarter 4, the most recent quarter. Fortunately, this information is also easy to come up with.

1. In the Qtr 4 column, click the arrow, and then click (Top 10...).

   The Top 10 AutoFilter dialog box opens. The information in steps 2 through 4 should be in the boxes by default. If it isn't there, do the following:

2. In the box on the left, click Top.
3. In the box in the middle, enter 10.
4. In the box on the right, click Items.
5. Click OK.

You now have the top sales for Quarter 4. But they're not listed in descending order, from the top sale to the lowest. That's easy to fix.

• In the Qtr 4 column, click the arrow, and then click Sort Descending.

Sort Descending on the drop-down list

The list is now sorted. That sale for Manjimup Dried Apples for $6,042.00 was a good one!

Top sales for Quarter 4 (sorted)

Find all sales that were less than $500

Now you want to show all sales in Quarter 4 that were less than $500 — to see where future opportunities may lie. To do this, use the Custom command.

First, return to the full worksheet:

1. In the Qtr 4 column, click the arrow.
2. On the list, click (All).

Next, apply the filter:

1. In the Qtr 4 column, click the arrow, and then click (Custom...).
2. In the box on the left, click is less than.
3. In the box on the right, enter $500.
4. Click OK.

To change the list to descending order:

• In the Qtr 4 column, click the arrow, and then click Sort Descending.

Now you have a list of sales that were less than $500.

Sales that were less than $500

Re-sort the list by customer

The current list is sorted by product, but you think it would be better to see the entire list sorted by customer.

Return to the full worksheet:

1. In the Qtr 4 column, click the arrow.
2. On the list, click (All).

Next, apply the filter:

1. In the Customer column, click the arrow.
2. On the list, click Sort Ascending.

The list is now sorted alphabetically by customer (A-Z), which makes the entire list easier to scan. But you can also filter this list to just one customer. If you want to see only the sales of BLONP, a company whose owner is another one of your manager's friends, do the following:

1. In the Customer column, click the arrow.
2. On the list, click BLONP.

BLONP customer sales

Analyze to your heart's content

You can see that filters are an easy, quick way to dive into your data and see the sales trends. Now you're ready to send a short report to your manager. (He might even ask you how you pulled the report together so quickly.)

If you want to take your analyzing skills to the next level, please see the article PivotTable reports for Sales 3: Compare your customers in the See Also box. This article uses the same data that is used here. Microsoft PivotTable® reports sound complicated, but once you learn how to use them, you'll have another powerful tool with which to analyze data.