Filtering Data
While sorting helps you arrange your data, filtering allows you to mask out certain data in the database and view just those records that meet the criteria used by the filter. You have two options for filtering data. First, you can filter the data in the database area using AutoFilter.
When you click the AutoFilter tool on the Main toolbar, a set of drop-down arrows appears at the top of each field name. You use these drop-down lists to select the filter parameters. Records meeting those parameters will remain visible in the database. Records not meeting the filter parameters will be hidden.
You can also set up simple and complex filters using the Standard and Advanced Filter dialog boxes. Standard filter parameters are set up right in the Standard Filter dialog box. Standard filters also provide you with the option of copying the results to another location on the current sheet or to another sheet in the current spreadsheet.
Advanced filters make use of a criteria range to set the filter criteria for the filter. And advanced filters can also be designed to output their results to a particular place on the current sheet or another sheet.
To Use AutoFilter
AutoFilter allows you to filter the database in place. It also allows you to filter by more than one field, but you have to set the filter criteria one at a time, since the AutoFilter operates using drop-down lists at the top of each field column.
1. |
Click anywhere in a defined database range or select the list that you wish to filter. |
2. |
Click the AutoFilter tool on the Main toolbar. Drop-down arrows appear at the top of each field column, providing a list of all the values found in that field. |
3. |
To sort by a particular field parameter, click the field's drop-down arrow (see Figure 8.9). Click one of the values on the list and database records will be filtered by that parameter. For example, if you only want to view the records for employees in a particular department, say the Sales department, you would select Sales on the Department field's drop-down list. Figure 8.9. Click a field drop-down arrow and select a field value to filter a database with AutoFilter.
|
4. |
To filter the database further based on another field's value, click the field's drop-down arrow and select the appropriate value from the list. Each time you filter the database, a smaller subset of records will be visible in the database area. |
5. |
When you have filtered the database with the AutoFilter feature, click the AutoFilter tool on the Main toolbar to restore all the records to the database (and remove the filter drop-down arrows from the field headings). |
To Set Up a Standard Filter
If you would like to be able to filter the database by more than one field and use logical (AND, OR) and mathematical operators (<, >, Largest, Smallest) in the filter, you need to create a standard query. For example, you could create a filter to find employees who are in one of two departments and have a particular salary range. The logical operator OR is used in the filter to specify that they can belong to a particular department such as Legal, or they can belong to another department altogether such as Sales.
To specify the salary range that you are using in the filter to pinpoint certain employees, you can use mathematical operators like less than or equal to (<=) and greater than or equal to (>=). Standard filters filter the database in place, just like AutoFormat. But, you can specify an area of the sheet or another sheet in the database that should be used to output the filter results (an extract range).
1. |
Click anywhere in the defined database area to begin. |
2. |
Select the Data menu, point at Filter, and then select Standard Filter. The Standard Filter dialog box will open. |
3. |
Select the first field that you wish to filter by in the first Field name drop-down box. |
4. |
Click the Condition drop-down list and select the operator that you will use in relation to the value you select for the filter (such as =, >, etc.). |
5. |
Click the Value drop-down list and select the value for the first field that you will filter by. |
6. |
To filter by a second field, you must select AND or OR in the Operator drop-down box. AND means that a record will have to match both field filters to be selected by the filter; OR means that a record can match either of the field values set to match the filter criteria. |
7. |
Set the field name, condition, and value for the second field. You can also add a third field to the filter if you wish. Figure 8.10 shows a standard filter that will select records in the database for employees that belong to the Marketing or Sales departments. Figure 8.10. Standard filters can filter by more than one field and use logical operators and conditions.
|
8. |
If you wish the results of the filter to be placed in a particular range on the current sheet or another sheet in the spreadsheet, click the More button. |
9. |
Click the Copy results to checkbox. Click the Shrink button to return to the sheet. Click on the cell (in the current sheet or another sheet) where you want the filter results to begin. |
10. |
Click the Shrink button on the dialog box rollup to return to the Standard Filter dialog box. |
11. |
When you are ready to run the filter, click OK. |
The results will appear in the database area. If you specified that the results should be copied to a specific range, the results will appear in that range (and not in the database area).
Note
You can use the Define Range dialog box to define areas on your sheet for the criteria or extract ranges that you will use with filters. Then when you need to specify an extract range in the Standard or Advanced Filter dialog box, you can specify the range by name.
If you did not copy the results of the filter to another location, you will eventually want to remove the filter from your database (so that you can view all the records). Select the Data menu, point at Filter, and then select Remove Filter.
To Set Up an Advanced Filter
Advanced filters allow you to filter a database by more than three fields. In fact, you can filter by as many fields as you like. Advanced filters use a criteria range to set the filter criteria. Advanced filters can act upon the database in place or they can copy filtered records to an extract range on the current sheet or another sheet in the database.
Before you can set up an advanced filter, you need to create a criteria range. The criteria range will consist of an exact duplicate of the field headings found in the database and one additional empty row. It is in the cells directly below each of the field heading copies that you will place the filter parameters for the advanced filter.
Copy your field headings from the database and paste them in other areas of the current sheet. Make sure you leave a blank row directly below the field names. This is where you will place the criteria for the advanced filter.
1. |
In the criteria range that you created, type the filter criteria directly below the field or fields that you wish to filter by. For example, to filter by department (setting Sales as the value), type Sales under the Department heading in the criteria range. Set other filter criteria as needed (you can use mathematical operators in your criteria such as =, <, > etc.). Figure 8.11 shows advanced filter criteria that will select records for employees in the Sales department that make more than $45,000. Figure 8.11. Place your advanced filter criteria in the criteria range.
|
2. |
Once the criteria are set, click anywhere on the database area. Then select the Data menu, point at Filters, and select Advanced Filter. The Advanced Filter dialog box appears. |
3. |
In the Advanced Filter dialog box, specify the filter criteria area, either by name or by using the Shrink button to return to the sheet and selecting the criteria headings and the criteria area below the headings. |
4. |
To output the results of the query to another area of the sheet or to another sheet, click the More button. |
5. |
Click the Copy results to checkbox and then specify the range for the output (see Figure 8.12). Figure 8.12. Specify the criteria range and extract range for your advanced filter.
|
6. |
When you have set all the advanced filter parameters, click OK. The filter will be run. |
If you ran the filter and did not have the results copied to another location in the spreadsheet, the filter will operate on the database in the database area. To view the records that have been hidden by the filter, Select Format, Rows, Show.