Hello everybody. In this video we'll talk about Advanced Filter. And most of you who have been using soft filter for decades would love this feature specially because all occasions filter fails to give you the desired results. And let me explain why a simple example. Before us lies hundreds of hypothetical names of different clients, the country in which they are located, the city in which their offices are located and exactly the location of the building. Now somebody asks you look, I want all the names of Park Plaza, all the client names which are under Park Plaza, and specifically those ones, which are more than 70,000.

And in addition to that, I also want the client names which are based out of Shanthi Nikitin building, and especially those ones whose amount exceeds 50,000. Now if you go by the traditional route, how would you proceed? You choose the data you will go to Data you will apply filter, then you definitely go to location. And then you intend to choose Park Plaza and Shanthi mckeithen correct this just halfway through. But now comes the tricky part. When you go to a mound, and when you say number filter greater then it doesn't differentiate between Which one should I assume for Park Plaza and which one should I consider for Anthony Keaton, for example, you may write is greater than 70,000.

But this 70,000 filter is going to be applied to both Park Plaza as well as shanty indicated despite the fact that you may have mentioned once again, and that is in the second box saying is greater than 50,000. And imagine what if there was a third criteria talking about a big house more than 80,000 were good, the third criteria B. Now if I press OK, notice Shanti Nikitin between 50 and 70 has not been included in this list. Why? Because The criteria was saying more than 70,000 overall. So this I've seen people using a lot of copy pasting techniques or basic manual sorting techniques to be able to get this, but I'll show you how this can be achieved through Advanced Filter.

First I'll help you walk through the basic nuts and bolts of Advanced Filter and then in the next videos I will show you the different variations that are embedded in Advanced Filter. So I go to sheet number four, I have a simple data on the left hand side the same data. Now using advanced filter which is residing under the Data tab, the button advanced, you will see how to get a simple list extracted which will say path plus or more than 70,000. So what I'm going to do is I'm going to click on Advanced. Now First things first, it is asking me, do you want to filter the list in its original place, or do you want to take it away and get the extracted value somewhere else? I'll say please copy to another location.

Means the original list will remain intact, the output will be somewhere out here from the cell that you will be choosing very soon. Now listed in let me delete the existing one, the list range is going to be the raw data including the headers, so I'm going to use shift control right shift control down. If I keep it chosen, and I press Ctrl backspace key notice it allows me to go to the top of the data and get the selection is still on from the f7 cell to eat 262 Now comes the criteria range. The criteria range has to be defined before you proceed with Advanced Filter. What I've done is I've picked up the headings location and amount, the spelling should be same, the format the colors could be different, but the spelling should be same. And within those headers are given Park Plaza and more than 70,000.

So I choose the criteria range. And the third option which says copy to Well, let me choose a cell here, which means the output is going to begin from Here, as I press OK, let's see what we get. I'm getting a list a refined list which says Park Plaza and the ones which are more than 70,000. So, this Advanced Filter is not self adjusting or self updating which means if you change any name here in the main list, this is not going to impact the output, you have to repeat the entire procedure again. So this was the basics of Advanced Filter. We will see more relations in the next video.