Filtering Data

While sorting rearranges spreadsheet data, Calc also lets you filter spreadsheet data to display only the data you want to see.

There are different complexity levels of filtering you can perform, represented in the following subsections. Advanced filtering isn't covered, because it involves working with spreadsheets as databases, which also isn't covered.

AutoFilter

AutoFilter is the quickest and easiest way to filter in Calc. It inserts a drop-down list button on one or more data columns that lets you select from a list of data that appears in the column(s). For example, if you have a spreadsheet that lists projects, deliverables, owners , due dates, and comments, you could set up an AutoFilter to let you select and view only the entries for one project, as illustrated in Figure 20-19.

Figure 20-19. Using an AutoFilter

To use AutoFilter:

1. In your spreadsheet, select the columns you want to use AutoFilter on. (Select a column by clicking the column letter.)

   If you want to use AutoFilter on all columns, don't select any columns. Just perform the next step.

2. On the toolbar, click the AutoFilter icon.

   

Run the filter by clicking the drop-down arrow in the column heading and choosing an item.

To view the entire spreadsheet after you select an AutoFilter item, click the drop-down arrow and choose All. If you choose Standard, the Standard Filter window is displayed to let you set up a standard filter. Choose Top 10 to display the highest 10 values only.

If AutoFilter is set up for more than one column, you can select one item with AutoFilter, then narrow the list further by selecting a second AutoFilter item. An AutoFilter drop-down arrow turns blue when you've selected an item in its column.

After you filter using AutoFilter, you can run the standard filtering tool to further pinpoint the data you want to see.

To stop using AutoFilter, click the AutoFilter icon on the toolbar. If for some reason your row numbers and column letters stop at the end of your spreadsheet data after you turn AutoFilter off, click the AutoFilter icon twice to turn to turn AutoFilter on and off again.

Standard Filtering

Calc's Standard filtering tool let you refine your filtering options further than you can with AutoFilter, letting you more pinpoint the exact data you want to view.

A main physical difference between AutoFilter and standard filtering is that AutoFilter provides a drop-down list of items to select from. Standard filtering simply applies the filtering options you've set and displays the relevant data.

1. Choose Data > Filter > Standard Filter.

2. In the Standard Filter window, set the filtering options you want. Use Figure 20-20 for guidance.

   Figure 20-20. Setting standard filtering options

   

3. Click OK.

The spreadsheet displays only the items that meet the filtering criteria. To turn filtering off and return to the original spreadsheet, choose Data > Filter > Remove Filter.