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 Column |
| 2. Activate the AutoFilter |
| 3. Start Filtering Data |
| 4. Apply Additional Filters |
| 5. Use Advanced Filtering Techniques |
| 6. 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."
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. |
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.
When you click West, Excel hides all the rows on the worksheet except for those that contain that text in this column.
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.
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. |
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. |
Note: Be sure to select the Or button or you will see no results at all.
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. |
| • | Track Finances with Excel |
| • | Use KPIs to Improve Profitability |
| • | Use Excel Template to Help Capture New Business |
| • | Office Small Business 2007 |
| • | Office Professional 2007 |
Tidak ada komentar:
Posting Komentar