Excel filters Data

Filtering a list allows us to quickly find and use subsets of data in a list. The filtering feature enables Excel to display only those rows that match one of the criteria we set, or those that match a set of conditions, and hide other rows. The AutoFilter and Advanced filter commands are available in Excel to filter the data. In general, AutoFilter can meet most of the needs. However, when we need to use complex conditions to filter the list, we must use advanced filter.

For a list of data, we can use two types of conditions in a conditional area. One is: For a single column, you can use multiple comparison criteria to specify more than two comparison criteria, for example, to show customers with arrears greater than 10,000, 100,000, or 1 million. The second is: when the conditions are calculated results or need to compare, you can use the calculation conditions. For example, if you display a row whose sales are larger than the quarterly average in cell H2, type = sales > $H $ (dollar sign indicates that the cell reference is an absolute cell reference).

12.4.1 filter data by using AutoFilter

If you want to perform an AutoFilter operation, you must have column tags in the list. The steps are as follows:

(1) Select a cell in the list of data that you want to filter.

(2) Perform the filter command on the Data menu, and then select the AutoFilter command from the submenu.

(3) Insert a drop-down arrow next to each column marker in the list, as shown in Figure 12-9.

(4) Click the arrow in the column that contains the data you want to display, and we can see a drop-down list, as shown in Figure 12-10.

(5) Select the items to display, and on the worksheet we can see the filtered results, as shown in Figure 12-11.