HOW TO USE ADVANCED FILTER IN EXCEL?

INTRODUCTION

In practical applications, in maximum cases , we have a large data which can’t be managed by just having a look on the data and manually selecting it. If we want to search for a particular data, we can do that by using control + F  and then typing but it is again a cumbersome task to use it over and again and type the search word.

For these reasons, we have got an option called FILTER which we discussed in another post. But FILTER deals with the simple properties and predefined formulas. What if the condition which we want is not present in the predefined conditions. For such cases there is an additional option known as the ADVANCED FILTER in EXCEL.

Advanced filters are used for more complex filtering process. It is done in a different style.

Lets try to understand it with the help of an example.

ADVANCED FILTER gives a dialog box to be filled as per the need of the criteria.

In this article we’d learn about the need of advanced filter and learn how to use the advanced filter to filter data for more complex situations.


STEPS TO APPLY ADVANCED FILTER IN EXCEL

STEPS:

  • Select the whole data sample and click Advanced Filter.
EXAMPLE DATA

STEPS:After clicking the ADVANCED button, a dialog box will open

AS WE HAD ALREADY SELECTED THE DATA, THE LIST RANGE WILL SHOW FILLED, IT CAN BE CHANGED TOO

The LIST RANGE should be the first cell up to the last cell of the data including the column HEADINGS.

If we click ok, nothing will happen at this stage. If we put a new STARTING CELL in COPY TO, it’ll copy the complete data to that particular location. (
Kindly select the radio button “Copy to another location” for this feature otherwise the field will be disabled.

SELECT COPY TO OTHER LOCATION

If you want to copy the data. Enter the starting cell.

DATA COPIED TO OTHER LOCATION


HOW TO ENTER CRITERIA IN ADVANCED FILTER IN EXCEL?

CRITERIA in the advanced option is written in almost similar fashion of the table.

STEPS:

  • Select a different area for the criteria.
  • Put the heading of the table(data on which the filter is to be applied) on the top. In this example,we have put Days and number of vehicle sold as headings same as the original data sample.
  • Enter the condition.
  • Enter the list of the cell containing the condition in the Dialog box in CRITERIA field.


HOW TO ENTER FORMULA

The formula is put in the condition cells as the following format

=”condition”

e.g. =”=SUNDAY” means to equate the days to SUNDAY. The first “=” will go away as you press Enter. If you want to show it you can do this by clicking CTRL+`. Although there won’t be any difference in the result.

Few more formula options are <,>,AND, OR ETC.

ALWAYS REMEMBER…

YOU CAN USE ANY FORMULA AS CONDITION, BUT THE RESULT SHOULD BE RETURNED AS TRUE OR FALSE. YOU CAN REFER FORMULA POST FOR FURTHER INFORMATION.

Criteria area created above the data table.

ENTER FORMULA

Select the CRITERIA cells in the criteria range and click OK

RESULT AS EXPECTED


HOW TO ENTER MULTIPLE CONDITIONS IN ADVANCED FILTER IN EXCEL?

MULTIPLE CONDITION

In the first case, we put the formula in the DAYS column to show only the SUNDAY vehicle list.

Now we want the SUNDAY where the sale was >40. So we put the condition under the Second column and repeat the procedure mentioned before.

PUT THE CONDITION AND CLICK OK.
RESULT AS DESIRED