Whole document tree
Chapter 12. Data Filters
12.1. Data Filters
With data filters you can select a subset of rows in the worksheet that meet the given criteria. You can, for example, copy rows of a table containing sales of departments whose profit has been exceptionally high into a new sheet simply using a filter.
12.1.1. Advanced Filter
To use advanced filter, you should have a few blank rows available in your worksheet to be used as a criteria range. These rows should not overlap with the rows in the table to be filtered.
Now copy all the column labels from the table you want to filter into the first blank row in the criteria range. Below the criteria labels, you can now type the conditions you want to match for the particular label. For example, under a label `Profit' you could type `>=1000'. The tool selects only rows that match all the criteria.
It is possible to have many conditions for a single label. For example, you can select the departments whose profit is either very high or very low. To do this, type, for example, `<=0' below the `>=40000' condition.
To start the tool, select ``Advanced Filter'' from the ``Data'' menu. It brings you the advanced filter dialog. In the dialog, select the action you want to take.
``Filter in-place'' writes the new table in-place. Note that you will loose all the rows in the table that will not match the criteria. ``Copy to a new location'' copies the selected rows into the same sheet but into the specified cell range. Type the cell range into the ``Copy to'' entry if you want this action to happen. The other options let you to copy the selected rows into a new sheet or a new workbook.
You should then specify the cell range containing the table to be filtered in the ``List range'' entry. The cell range containing the criteria is specified in the ``Criteria range'' entry. If the original table contains duplicate rows, you may also want to specify the ``Unique records only''. If it is checked on, the filter removes all duplicates.
To start the tool, you can then click the ``OK'' button and you will get the new table.