In this video I am going to show you how to use the Advanced Filter in Excel
With the advanced filter you are able to filter by many criteria and copy the results to another
location.
Here I have a large amount of data.
to set up the advanced filter we need three things
1.
Source 2.
Criteria 3.
Destination
The first thing we will do is select our criteria.
We would like to sort by one specific type of sale
So I select the type heading and copy it.
I am going to the "M" column.
I would like to filter by fuel Select fuel and copy it and paste it below the type
heading that we have copied to column M
The other criteria that I would like is the salesperson
I am going to go select the sales person We will copy the Salesperson heading and paste
it in the "N" column next to the type
I would like to select John as the salesperson.
So I am going to select him and copy him below the salesperson
Now we will click in the middle of your data
we will go to the data tab and in the Sort & Filter section of the ribon we will select
advanced filter
we get this dialog box up and
the first thing we will select is the Copy to another location
since we have selected a cell in our range it has decided that this is the range we would
like to do
so now we are going to do is select the two headings and the two criteria
Now we are going to click in the cell to copy to and we are going to select a location that
we would want this to go
We will select a couple of rows below our criteria.
Click O.K.
Now I will double click to correct the size of these columns
so now you see that you have all the fuel that John sold filtered out of this data.
lets show you how to filter your data to another worksheet.
So what I am going to do is select all of this and press CTRl -Shift-down arrow
And then I am going to my home tab and selecting clear all
Now I would like to define a day so I am going to select billing date and we are going to
go ctrl c and we are going to put that in the column next to sales person ctrl v
Lets pick a day lets say 7/8/2015 and we are going to put it here and ctrl V
go to insert sheet2 and select a cell
then we are going to our data tab and selecting advanced filter
it needs to be the active sheet for the data to place out data as a result of our filter
so what we are going to do is we are going to copy to another location
Then we are going to sheet1
we are going to select the headings at the top here and press CTRl -Shift-down arrow
and now you can see that it has selected all nine thousand records
we are going to click in the criteria range and it is going back to sheet 2 and that is
just fine
we will go back to sheet 1 here and then we are going to the top and we are going to select
the type, sales person billing date
We are going to the Copy to and then we will go to the B2 - sheet2 then we go O.K.
And there you have the information filtered from sheet1 and placed on sheet 2 based on
the criteria that you have described
So that is how you use the advanced filter in excel
Please subscribe
• • Select a cell on sheet2.
• Click on the Advanced Filter button
• Click in the list range button and then select sheet 1 and the entire data range
• You will be returned to sheet2 • Click in the criteria range and then go
back to sheet1 and select your criterion range • Click ok and the data will appear on sheet
2