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
- Select the whole data sample and click Advanced Filter.
STEPS:After clicking the ADVANCED button, a dialog box will open
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.
If you want to copy the data. Enter the starting cell.
HOW TO ENTER CRITERIA IN ADVANCED FILTER IN EXCEL?
CRITERIA in the advanced option is written in almost similar fashion of the table.
- 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
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.
Criteria area created above the data table.
Select the CRITERIA cells in the criteria range and click OK
HOW TO ENTER MULTIPLE CONDITIONS IN ADVANCED FILTER IN EXCEL?
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.