PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

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.
apply advanced filter excel

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

how to apply advanced filter in excel
As we had already selected the data, the List range will show filled, it can be changed.

The LIST RANGE should be the first cell upto 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.

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.

Criteria area created above the data table.

Select the CRITERIA cells in the criteria range and click OK

advanced filter in excel
Result as per expectation.

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.

multiple criteria in excel filter formula
PUT THE CONDITION AND CLICK OK.
ADVANCED FILTER EXCEL
RESULT AS DESIRED.

OTHER WAYS TO REACH THIS ARTICLE

  • ADVANCED FILTER IN EXCEL, PUT MULTIPLE CONDITIONS IN EXCEL FILTER, FILTER IN EXCEL, SIMPLE UNDERSTANDING OF FILTERS IN EXCEL

YOU MAY LIKE

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: