In this example, we will illustrate how to use the advanced filter to show results based on a user defined criteria.
To perform the advanced filter, you need to specify a list_range and a criteria_range.
list_range – The range of cells that need to be filtered.
criteria_range – The user defined criteria to be used for the advanced filter.
To display sales of product AA made in the USA. Execute following steps:
1- Set the criteria_range, highlighted for illustration only.
2- Go to the Data tab, in the Sort & Filter section, select Advanced option. Or press Alt + A + Q.
3- defined list range and criteria range in the Advanced Filter box and press OK.
4- Result. Excel will show sales of product AA made in the USA only.
Formula as Criteria
To display sales of all products made in the USA above 2000 units, execute the following steps:
1- Enter criteria and following formula =D4>2000.
2- Press Alt + A + Q. Select both list_range and criteria_range.
Previous Lesson: Date Filters