The filter is a simple and reliable way to locate and manage data in a spreadsheet. In Excel 2007, you can filter data using the AutoFilter feature, which displays only data that meets the specified criteria. Filtered data can be copied, edited and printed without having to move it to a new spreadsheet. Using the automatic filter, you can filter data according to alphabetical, numeric or color conditions by selecting specific criteria from a list. Here we show you how to use the AutoFilter feature found in Excel 2007.
Steps
Method 1 of 2: Applying the filters
Step 1. Open the spreadsheet in which you want to filter the data
Step 2. Prepare the data for an Excel 2007 autofilter
Excel can filter data in all selected cells within a range, until it finds completely blank rows or columns within the selected range. Once an empty column or row is found, the filter stops. If the data of the range you want to filter is separated by empty columns or rows, you must remove them before proceeding with the automatic filter.
- Conversely, if there is data in your worksheet that you don't want to be included in the filtered data, separate the data using one or more blank rows or columns. If the data you don't want to filter is below the data you want to filter, use at least one completely blank line at the end of filtering. If the data you don't want to filter is to the right of the data you want to filter, use a completely blank column.
- It is also good practice that the column headings remain within the range of the data to be filtered.
Step 3. Click each cell of the range you want to filter
Step 4. Click the Data tab of the Microsoft Excel ribbon
Step 5. Click Filter in the Sort and Filter group
Drop-down arrows will appear at the top of each column range. If the cell range contains column headings, drop-down arrows will appear on the headings themselves.
Step 6. Click the drop-down arrow of the column containing the desired criteria to filter
Do one of the following:
- To filter the data by criteria, click to clear the Select All check box. All other check boxes will be cleared. Click to select the check boxes corresponding to the criteria you want to appear in the filtered list. Click OK to filter the range for the selected criteria.
- To set a filter by number, click on Number Filters and, from the list that appears, choose the desired comparison operator. The Custom Auto Filter dialog box will appear. In the box to the right of the comparison operator selection, select the desired number from the drop-down list box or type the desired value. To set the numerical filter with more than one comparison operator, click on the round button of the logical operator E to indicate that both criteria must be true or click on the round button marked O to indicate that at least one criterion must be true. Select the second comparison operator and then select or type the desired value in the box to the right. Click OK to apply the number filter to the range.
- To filter the data by color-coded criteria, click Filter by Color. Click the desired color from the Filter by Font Color list that appears. The data is filtered by the selected color.
Method 2 of 2: Remove the filters
Step 1. Click the drop-down arrow of the range containing the filter and then click Clear Filter from Column Header:
you will remove the filter from that column.
Step 2. Click the Data tab of the Microsoft Excel ribbon and then click Clear to clear the filters from all columns
Advice
- When you set up filters, you can sort the data as needed. You can sort the data alphabetically, ascending with Sort A to Z or descending with Sort Z to A. You can list numbers in ascending order with Sort Smallest to Largest or vice versa with Sort Largest to Smallest. It is also possible to sort the data according to the font color.
- To update the results of applying data filters, click the Data tab of the Microsoft Excel ribbon and then click Reapply.
