If you can’t get the results you want from a custom filter, you can construct your own advanced filters to get the most control over your data lists.

To make an advanced filter, you have to set up a range of criteria to filter your list with.

In this example, we want to show only the records where the Age field is less than 40. To do this, you can type a column heading in cell F1. This heading must be exactly the same as the heading in the data list that you want to base the filter on.

In cell F2, you can enter the criteria. In this instance, cell F2 contains <40 as you only want to show records with an age field value that is less than 40. To apply the filter, click on any cell in the data list, and choose Data->filter->Advanced Filter from the menu bar. You will now see the data list outlined with a flashing dark and light border, and the Advanced filter dialogue box will be displayed.

When you see the box, make sure the list range is indeed the range you want to filter. You can then click in the data area labelled Criteria range: and select the cells that contain your filtering criteria (cells F1:F2) in this case.

The filter the list in-place radio button is selected, so the filtered records will appear in the same location as the original list.

Click OK to filter the List range using the Criteria range as specified in the Advanced Filter dialogue.

The list has been filtered to show only those records that have an age field value that is less than 40.

To display the full list, click a cell in the list, and choose Data->Filter->Show All from the menu bar.

You can set up more complex filters by adding to your criteria range. For example, the following criteria range will filter the records so that only those with an age less than 40, and a height greater than 63, and a weight less than 200 will be shown. Notice that the column headings in the criteria range are identical to those in the original list.

This next criteria range will show records that have a height greater than 63 or a weight less than 200. The conditions are offset by one row from each other, indicating the use of a logical or.