Add a Filter in Excel 2007
Filtering is a convenient and reliable way to locate and manage spreadsheet data. You can filter data using the AutoFilter feature in Excel 2007 to display only the data that meets specified criteria. Filtered data can be copied, manipulated, and printed without having to move it to a new spreadsheet. Using AutoFilter, you can filter data by selecting criteria from a list, by numerical conditions, or by color. Here's how to use the AutoFilter feature in Excel 2007.
Contents
Steps
Applying Filters
- Open the spreadsheet in which you want to filter data.
- Prepare your data for an Excel 2007 AutoFilter. Excel can filter the data in all selected cells within a range, as long as there are no completely blank rows or columns within the selected range. Once a blank row or column is encountered, filtering stops. If the data in the range you wish to filter is separated by blank rows or columns, remove them before proceeding with the AutoFilter.
- Conversely, if there is data on the worksheet that you do not want to be part of the filtered data, separate that data using one or more blank rows or blank columns. If the data you don't want to filter is located beneath the data to be filtered, use at least one completely blank row to end filtering. If the data you don't want to filter is located to the right of data to be filtered, use a completely blank column.
- It is also good practice to have column headings within the range of data being filtered.
- Click any cell within the range that you would like to filter.
- Click the "Data" tab of the Microsoft Excel ribbon.
- Click "Filter" from the "Sort & Filter" group. Drop-down arrows will appear at the top of each column range. If the range of cells contains column headings, the drop-down arrows will appear in the headings.
- Click the drop-down arrow of the column containing the desired criteria to be filtered. 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 of the criteria that you want to appear in the filtered list. Click "OK" to filter the range by the selected criteria.
- To set up a number filter, click "Number Filters" and then click the desired comparison operator from the list that appears. The "Custom AutoFilter" dialog box appears. In the box to the right of the comparison operator selection, either select the desired number from the drop-down list box or type the desired value. To set up the number filter by more than one comparison operator, click either the "And" radio button to indicate that both criteria must be true, or click the "Or" radio button to indicate that at least 1 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 font color.
Removing Filters
- Click the drop-down arrow of the range containing the filter and then click "Clear Filter from Column Heading," to remove filtering from one column.
- Click the "Data" tab of the Microsoft Excel ribbon and then click "Clear" to clear filters from all columns.
Tips
- As you set up filters, you can also sort the data as needed. You can either sort data in ascending "Sort A to Z" text order - "Sort Smallest to Largest" numerical order, descending "Sort Z to A" text order - "Sort Largest to Smallest" numerical order, or you can sort the data by font color.
- To update the results of filtering to data, click the "Data" tab of the Microsoft Excel ribbon and then click "Reapply."
