Advanced Filter

It is used to filter selected range but works differently from Filter command.

It displays the Advanced Filter dialog box instead of the AutoFilter menu.

You have to set the criteria to filter separately from the data range. You can put above your data or anywhere as long as outside from your data.

How to use:

• Say you have a set of table like below in range A1:D21

• Type your criteria anywhere. You MUST specify each header that you want to filter and its value. Cells in yellow are the header title. Example as per below table. Criteria in range G1:I2.

• Sector = 3, Region = South and Sales = any value. Now go to Data → select Advanced in Sort & Filter.

• A dialog box will be displayed to ask for the range that you want to filter and criteria range for criteria that you want to filter. Just select the range accordingly and then click OK. Example as per below picture. Refer the box border color to differentiate between data range (red) and criteria range (green).

• Result as follow:

• You can also add more criteria like below. If you want to filter a column that contains number, you can use = for equal to, > for greater than, < for less than >= for greater than or equal to, <= for less than or equal to.

• Result as follow: