Filtering Data

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.

5.12.1. Auto Filters

This section has not yet been written.

  • Alt+Down Open the AutoFilter in the current cell.
  • Down and Up Changes the selected item in an AutoFilter
  • Home Select the first item [All] in an AutoFilter
  • End Select the last item in an AutoFilter
  • Enter or Alt+Up Apply the current entry in an AutoFilter list, and close the combo-box.

5.12.2. 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.

Figure 5-24Worksheet containing the table to be filtered and a simple 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.

Figure 5-25Criteria for selecting rows whose `Profit' column is between 0 and 40000.

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.

Figure 5-26The Advanced Filter dialog.

``Filter in-place'' writes the new table in-place. Note that you will lose 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.