Using Advance Filter sample 1 pg 130 131 132

Using Advanced Filter

Example 1

To see only rows where column MTH contain more than one particular month.

Say we want to see the data for months 1, 2 and 3 only. This can be done by selecting rows having items 1, 2 or 3 in column MTH.

In this case, we want to select three unique items from a column. So we will use an Advanced Filter operation. This will allow us to select more than 2 unique items in a certain column.

1. To use an Advanced Filter operation, we need a rectangular block or range of cells to contain the criteria. This block of cells is called the criteria block. Preferably, the criteria block should be located in a separate sheet, for easy reuse. In this book, the sheet is named Criteria, to reflect what it will contain. You can name it differently. In the criteria block, the topmost cells contain titles, and the cells immediately below this contain the list of unique items that we want to see.

NOTE

The spelling of the titles in the criteria block must be the same as that used in the data title in the data area. Also, the spelling of the items must be the same as those in the data area.

130

2. Set up a criteria block to look like this:

	A
12	The following block is used to select rows of more than one month in column MTH.
13
14	MTH
15	1
16	2
17	3
18	

In this example, the criteria block shows that we want to select rows where column MTH contains the item 1, 2, or 3.

3. After setting the above criteria, activate the sheet DATA.

NOTE

To use an Advanced Filter operation, it is not necessary to turn on filtering mode.

4. Click menu Data/Filter/Advanced Filter.

5. The Advanced Filter dialog box will appear:

Advanced Filter					?X

Action
	Filter the list, in-place
	Copy to another location

List range: 	$A$4:$U$563
Criteria range:	Criteria!$B$14:$B$16

Copy to :		(Blank)

☑ Unique records only

OK		Cancel

131

6 The List range box will be automatically filled with the address of your data area.

7. Fill the Criteria range box with the area of your criteria block.

TIP

	Here is an easy way to select the criteria range:

	1. Ensure that the insertion bar is in the Criteria range box by clicking inside the box.

	2. Activate the sheet where the criteria block is available (in this book, it is sheet Criteria).

	3. Use your mouse to highlight the criteria block.

Automatically, the Criteria range box is updated with the address of the highlighted area.

8. Click OK.

Now, you should see the data filtered according to the criteria block. All the totals in row 1 will be automatically updated.