Advanced filter

This enables you to select rows by setting your own criteria. For instance, you may have several sets of criteria that you want to apply to a particular list or lists. You can store these criteria in your spreadsheet and use them when whenever you want to.

Using the previous example, we will set up a simple Advanced Filter.

The rows that are to contain the Advanced Filter criteria should be empty and preferably above the list that is to be filtered. There should be at least a one-row gap between the filter and the list.

Our example contains columns for Months and Sales, the titles acting as Labels. We will create a filter that selects those rows in which the sales are above a certain value (see below for more information on using criteria):

  1. Type in Sales (or copy and paste the title from the list) in a cell on an unused row.

  2. Enter >10,000, say, in the first cell under the Sales label.

  3. Select Filter from the Data menu and then select Advanced Filter. The Advanced Filter box is displayed.

  4. In List you enter the range that contains the list to be filtered, and in Criteria the range that contains the filter criteria you want to apply to List.

  5. First, click on the Collapse Dialog button at the right of List. This temporarily collapses the dialog box and enables you to select the list range. Use your mouse to drag a border round the list, including the labels. Click on the button again to reopen the dialog box. The range details are shown in List.

  6. Click on the Collapse Dialog button at the right of Criteria, select the criteria range, including the label, and then click on the button again to reopen the dialog box. The criteria range details are shown in Criteria.

  7. At this point you can immediately apply the filter by clicking on OK. The selected rows from the list will be shown and the others will be hidden. In our example, these will be all the rows with a value above 10,000 in the Sales column.

  8. However, you may want to leave the list rows intact and instead copy the data from the selected rows to somewhere else in your spreadsheet. To do this, click on the Copy to checkbox to activate the corresponding range box. Click on the Collapse button and then click once on a single cell elsewhere in the spreadsheet. Make sure this is away from cells with data. At this point, before you apply the filter you will not know how many rows the filter will require for its result, so it is best to leave enough space. Note that any data that is already in the copy range will be replaced by the filter data.

  9. If you also want to copy formats, click on the Copy formats checkbox.

  10. Click on OK to close the dialog and apply the filter.