79. Filter Data That You Want to See
BEFORE YOU BEGIN
77 Import Data into a Calc Database
78 Sort Calc Database Data
75 Create a Calc Database
80 Compute Table Totals and Subtotals
Databases can grow to be enormous . Without some way to filter the data, finding what you want is tedious . Calc's Find and Replace command works well enough to locate values that you want to find, but by being able to apply a filter to your database, you can actually hide data that does not currently interest you without removing that data from your database. When you're done with the filtered data, you can easily return to the full database view.
Calc supports two kinds of filters, both of which are related :
AutoFilter filters, where you specify values to filter by
Standard dialog box filters, where you can specify a range of values to filter by
AutoFilter A Calc database filter where you select from a list of values to filter by and view.
If you delete or format cell ranges that include rows currently hidden by an auto-filter, those rows are not affected by the deletion or formatting. For more information on formatting cells , see 60 Format Cells ; for more information on deleting cells, see 64 Edit Cell Data .
Request a Standard Filter
Once you define your data range, you can request a filter by selecting Data, Filter, Standard Filter . Calc displays the Standard Filter dialog box.
| | Specify the Filter Criteria
Select from the Field Name
dialog box. All the fields defined by the data range's column names will appear when you open the Field Name
list box. Select a condition in the Condition
list box, such as an equal sign or less-than sign, and then enter a quantity (you can also click the Value
list box's down arrow to see a list of possible values) stating what you want to filter by. For example, if you enter UnitsPrice
, and 9.99
, you are requesting that you only want to see the products that are priced less than $9.99.
79. Filter Data That You Want to See
You may add additional criteria to filter down your data even further by selecting AND
from the Operator
column and entering second and even third criteria.
Click the More button to see additional filter options, such as the ability to make your criteria case sensitive so a match is made against text fields only if the uppercase and lowercase letters match your criteria's uppercase and lowercase letters . Also, you can request that the filtered data be copied to a range you specify instead of the filtering taking place right in your data range itself.
to apply the filter and view the results.
Remove the Filter
To restore your data to its original state once you've viewed , printed, stored, or saved the filtered data, select Data, Filter, Remove Filter to remove the filter and return your complete data range to your spreadsheet.
Use the AutoFilter
To use the AutoFilter feature, select Data, Filter, AutoFilter. Arrows appear to the right of each field name.
When you click one of the field name arrows, a list of values opens to display all possible unique values in that field, with scrollbars if needed to display the entire list. When you select any value in that list, Calc immediately filters on that value, displaying only those records that match that criteria. For example, if you click the UnitsInStock field name arrow and select 12 , only the products with an inventory of 12 will appear in the list.
Once you create the filter, you can print, sort, save, or copy the filtered data and return to the full database by selecting Data, Filter, AutoFilter once again to remove the check mark next to the command.
For quick filters when you want to filter based on exact matches, use the AutoFilter feature, which is faster than displaying the Standard Filter dialog box every time you want to filter the database.
If the Tools toolbar is open, you can access the AutoFilter feature by clicking the AutoFilter button. To open the Tools toolbar, choose View, Toolbars, Tools .
Use the Top 10 option in the AutoFilter list to see the 10 records with the highest value in the field name column you chosefor example, the 10 highest prices or the 10 top sales amounts.