Highline Excel 2016 Class 19: Transform Data Sets using Advanced Filter (8 Examples)

Download Files: https://people.highline.edu/mgirvin/A...
In this video learn about:
(00:14) Introduction to Advanced Filter
1. (02:50) Extract Records based on AND Logical Test (AND Criteria). Comprehensive Example including Defined Names that are created and how to run the Advanced Filter a second or third time.
2. (09:48) Extract Records based on OR Logical Test (OR Criteria)
3. (10:39) Extract Records based on "Contains" Criteria ("Partial Text" Criteria)
4. (12:00) Advanced Filter with Complex Criteria
5. (13:00) Advanced Filter Extract to Different Sheet. Extract Data for a given year. See how to create formulas to create criteria base on year. See the functions, DATE and TEXT.
6. (18:00) Advanced Filter to get a Unique List from Column
7. (19:53) Advanced Filter with Helper Column Formula to Create Table Report with Professional and Neat Formatting and Page Setup. See the functions: AND and SEARCH.
8. (29:32) Advanced Filter with Formula Criteria on Large Data Set to Create Report

---

Closed Caption:


Welcome to High Line Excel
2016 class video number 19.
If you want to download this
workbook, business 218 video 19
Start or the finished file or
the PDF file, click on the link
below the video.
Hey, we got to talk about
the amazing advanced filter.
And we're going to use it
to transform data sets.
Here's a bunch of examples
we're going to do.
Let's start by
going over to AF1.
Now Advanced Filter.
What does Advanced Filter do?
Advanced Filter is useful
when you have complex criteria
and you want to extract
records and/or fields
from a proper data set.
And you don't need it
to update upon refresh.
If you needed to
have it refresh,
we'd be using Power Query.
How is it different
than the filter feature?
It's different
than filter feature
because it allows you
to place the specified
criteria in the cells
in the worksheet
rather than hidden behind
the Filter drop down.
Now look at this down here.
Our first example is we
need to get the records that
match the criteria.
The date is greater than
or equal to 10/1/2016.
And the date is
less than 3/31/2013.
And the customer equals Amazon.
Not only that, but we need
the field's region, product,
and sales.
We're actually going to
put all of this criteria
down on the face
of the spreadsheet.
We'll be able to see it
and change it and then
re-run Advanced Filter.
Remember if we were
using the Filter feature,
we just have a little drop down.
We would select the criteria.
But it would be hidden
behind the scenes.
We would have no visual
indicator of what criteria
we selected.
How is it different
than Power Query?
It's different from Power
Query in that it does not
automatically update when
you click the Refresh button.
We can re-run Advanced Filter.
But it takes a few steps.
When to not use Advanced Filter?
If you only have a few
criteria for extracting records
from the data set, then
use the Filter feature.
If you just need to
get West and Gigi
and you don't need
to see the criteria
and you're not going to rerun
it a bunch of times, then by all
means use Filter.
And we saw lots of examples of
AND criteria and OR criteria
and complex criteria
using the Filter feature.
So if it's simple,
use the Filter.
The other main reason we don't
want to use Advanced Filter
is if you need the process
to update when you click
Refresh, then use Power Query.
When do you use Advanced Filter?
When you have complex criteria.
When you need to
see the criteria
on the face of the spreadsheet.
And you don't need the process
link to the source data.
Now let's look at
our first example.
We're always going
to have three areas.
The data set area.
That's just a proper data set.
Field names at the top.
Records and rows, empty
cells all the way around.
The criteria area.
We're actually going to
have to list field names
and criteria below.
Now we already know how to do
this criteria area fluently
because we use D functions
back in video number six.
And the way we set
up the criteria area
is exactly the same
as D functions.
And the third area
is the Extract area.
In the extract
area, that's where
the records will be pasted.
And that's where
we list the fields
that we want to tell
Advanced Filter to extract.
All right.
Let's do our criteria.
As you recall, we have to
put comparative operators
and the criteria.
So I'm going to say 10/1/2016.
So that's greater than or
equal to 10/1/2016 tab.
And we want it less than
or equal to 3/31/2017.
And we want it equal
to the customer Amazon.
Now if I type Amazon like
this, that contains criteria.
That means it would search
through the customer
column over here and find any
item that contained Amazon.
Now for this example,
it wouldn't matter.
Because there's no Amazon
Freshes or new Amazon
in this column.
So we wouldn't have any problem.
But if we want
exactly something,
let's use the proper
comparative operator which
is the equal sign.
But as you recall from
our study of D functions,
you cannot put an equals sign
in text because Excel will think
it's a formula.
So the trick is you
put a lead apostrophe.
A lead apostrophe says hey.
This is text.
There's our criteria.
You have to have the field
names and criteria below.
Now for an AND
logical test, you list
all of the criteria
on the same row.
Not only that, but
of course if you
have criteria looking
at the same column,
you repeat the field names.
Now I want to remind
ourselves about D functions.
Here's our proper data set.
Here's our field
names and criteria.
If our goal is to add sales,
we can simply use the D sum.
Remember D sum says hey,
where's the database?
I'm simply going to
click on the top cell.
Control-Shift.
Right arrow.
Down arrow.
Control.
Backspace.
So when we get to
Advanced Filter,
we're going to have to Show
Advanced Filter the database.
Just like our d functions.
Comma the field.
This is different
than Advanced Filter.
But for D sum,
since we're adding,
we need to tell D sum which
field we're adding upon.
Now I'm going to click on Sales.
Then I'm going to click on the
field argument in the screen
tip and hit F9 to evaluate.
Because I want to
hard code that in.
By putting sales in
to field argument,
it tells D sum
which field to add.
And our criteria.
There's our field names
and criteria below.
Now we're just
doing this to pull
the parallel that
the criteria area
and having a proper data set
is exactly the same for our D
function.
So when I hit Enter,
there is the total.
Now remember Advanced Filter
actually extracts records.
And we don't want
just the total.
We want to see all the records
for the column's region,
products, and sale.
Now how do we do this?
We click in a single cell.
Data.
And there it is.
We click Advanced.
And there's our dialog box.
I'm going to use the keyboard
throughout this whole video.
So I have a single
cell selected.
ALT-A-Q.
Now it got the data set
right because we have
empty cells all the way around.
We want to say Copy
To another location.
There is the list range.
The criteria range.
Field names.
Criteria below.
Tab.
And Copy To.
This is the text box where, if
we click a single empty cell,
it will extract
all of the fields
and list the field names.
But that's not what
we're going to do here.
And in most of our
examples, we will only
be interested in
certain columns.
So I highlight one, two, three.
Now those three column say hey
only get those three columns.
All of the records will
be placed below this.
Now you want to be careful.
Don't have anything below
where your extract area is.
Because every time
Advanced Filter runs,
it deletes everything below.
And that's it.
We'll talk later
about unique records.
But that's it for us.
When I click OK, boom.
Just like that, it got all of
the records between these two
dates and for the
customer Amazon.
Click in the top cell.
Control down.
So it got a bunch of records
that matched that criteria
that we list in the criteria
area and the criteria for which
columns to extract.
Now if we change the
criteria, if I come up
and I change this
to OD customer,
we can see some ODs over here.
Now when I rerun this, I'm
going to click in a single cell
ALT-A-Q. What?
It remembered.
Now before we re-run this,
I'm going to click Escape.
And we want to go
look what happens.
There are two defined names.
One for criteria area
and one for extract area.
If we go up to Formulas,
Name Manager and click or use
the keyboard Control
F3, there are the names.
Now criteria and extract.
Those our names on this sheet.
If we in this workbook create
a bunch of advanced filters
on different sheets,
there will be a criteria
define name and an extract
area for each one of the sheets
when we use criteria
area and extract area.
I'm going to click Close.
Now let's re-run with our
new criteria Advanced Filter.
Click in a single cell ALT-A-Q.
Copy To another location.
And look at that.
It remembered the list range.
That's our proper data set.
Our criteria area.
If I click here,
you can see it's
got field names,
criteria below, and Copy
To is our Extract area with
the range of cells of our field
names that we want to extract.
When I click OK, it
completely changed.
And if I go down here,
those are all the ODs.
Now I'm going to do one more.
I'm going to change
this to 3/1/17.

Enter.
ALT-A-Q. You actually don't
need to click in a single cell
any subsequent times
because it's all memorized.
Copy To another location.
And click OK.
And just like that it got
only March 2017 Customer OD.
Now you could see it left
the formatting down below.
But all of the records
itself have been deleted.
All right.
Now let's go over
to our next sheet.
AF2.
And now we want to
talk about OR criteria.
Just as we talked about
with our D functions,
OR criteria is listed
on different rows.
AND is always on the same row.
OR is on different rows.
I'm going to click
in a single cell.
ALT-AQ.
We got the list because
we had a proper data set
with empty cells
all the way around.
I want to copy to criteria
range, field name, and OR
criteria below.
Tab the Copy To.
We have different
columns we want now.
D. Product and sales.
When I click OK,
there are our records.
That is simply amazing.
Using OR criteria, these are all
the records for date, product,
and sales for Amazon or Google.
Let's go over to AF3.
We want to talk about
Contains criteria.
Our goal is to get records
that match this criteria.
Dates greater than
or equal to 1/2017
and less than or
equal to 1/4/2017.
So only four days.
And any region
that contains West.
So you could see over here
we have West and Midwest.
We might have
Northwest or Southwest.
So what do we do?
We don't put an equal sign.
We just type West.
So there are our
three AND conditions,
our field names,
criteria in essence
for which fields to extract.
I click in a single cell
of our proper data set.
ALT-A-Q. Copy To
another location.
The criteria area.
There it is.
Between these two dates
and contains West.
Tab-Copy To.
I'm very carefully going to
highlight these two field
names that's telling
hey, Advanced Filter.
Only get date and sales columns.
When I click OK, there it is.
Our four records.
If we were to highlight this
and look down on the status bar,
there's our total for sum.
If we look up here,
our D functions.
Remember.
They work exactly the same way.
And we get exactly
the same number.
Now let's go over to AF4.
Here's complex criteria.
Our question is
hey, Date column.
Please find all records
that are greater
than or equal to
1/1/2017 and less than
or equal to 3/31/2017.
And a sales rep Gigi
and the customer Amazon.
Or all of this is
AND but it's OR.
It's like there's an OR
right in between there.
Or records between these
two dates and sales
rep Gigi and customer OD.
I'm going to click
in a single cell.
ALT-A-Q. Copy To
another location.
Criteria.

Field names.
And AND OR criteria below.
Tab.
Copy To.
I'm going to tell it only give
me date, product, customer,
and cost of goods sold.
Now I click OK.
And just like that I've
extracted the records.
That is absolutely amazing.
Now I'm actually going to
jump over to example six
before we do five.
Here's our data set.
Transaction, daytime sales
rep, region, website, product,
units, discount, price,
type, and net revenue.
And our goal is on a different
sheet to extract the year 2017.
Well I'm going to
start over here.
And I'm going to copy
the field name Control C.
I'm going to come
over here to AF6.
Click Control-V. There's the
column Header for the field
name.
And below it I
need the criteria.
I do need two because I'm going
to go between the beginning
and end of 2017.
But actually I'm going to
do some formulas over here.
I'm going to say Year,
Control-Enter, Control-B-Enter.
And I'm going to
type the year 2017.
I actually want to
simply put a year here
and have the criteria totally
update based on this year.
And then we'll have
our fields down here.
All right so the first formula.
I'm going to use
the Date function.
But before I can use the Date
function, I have to say equal.
And in double quotes, I
have to say greater than
or equal to and double quote.
Join it.
And I'm going to use
the Date function.
The year?
There's the year.
Comma the month one.
Comma the day one.
Remember.
Date function creates
a serial number date.
Now it's going to
kind of be shocking
when I hit Enter
because remember,
formulas can't see
number formatting.
Although Date function in a cell
delivers a number formatting.
In a formula it doesn't do that.
Now Advanced Filter works
perfectly just like that.
You could leave it like that.
If you don't want to
see the serial number,
you want anyone who looks at
this to see a visual date,
then we have to use
the Text function.
We learned about Text functions
back in video number eight.
There's the value.
Comma.
And the Text function
takes a value.
And if we know a custom
number formatting,
it will display it as
text in double quotes.
MM/DD/YYYY.

End double quote.
Close parentheses.
That's pretty fancy.
And there's are greater than
or equal to when we change this
to 2016, instantly that updates.
Control-Z.
Now I'm going to cheat and
come over here and just copy
this whole thing in Edit mode.
Control-C-Escape.
Put it in Edit mode.
Control-V. And I very carefully,
because this is the upper date,
I need to say anything
less than or equal to.
And instead of the
month one, we put two.
And instead of the
day one, we put 31.
And there we go.
All right so I added
some formatting
and actually moved
all this down.
These are the same
formulas right here.
And we want to try this.
Now let's click
in some empty cell
and do ALT-A-Q. And what
happens is we're on a sheet
without a data set.
Our cursor was
not in a data set.
So of course, it didn't
know where the data set was.
Now the cool thing
about this is,
I can actually
tell this text box
to go on any other
sheet in this workbook.
I can also click into
a different workbook
and extract from a
different workbook.
So List Range, our data
set is on the sheets.
AF6 data sheet.
So I click there.
I click in cell A1.
Control-Shift right arrow.
Control-Shift down arrow.
And now I'm going to hit Tab.
And when I hit Tab, it
goes to the next text box
and automatically brings
me back to the sheet
where I started this.
Now I actually want to click
Copy To another location.
The criteria range.
There it is.
Tab.
And Copy To.
Now in this example, I'm going
to click in an empty cell.
And that tells Advanced Filter
to extract all of the fields.
So when I click OK, there it is.
There's all of my columns
and all of my records,
but only for the year 2017.
If I click Control down arrow,
that's a lot of records.
That's all the way
down to row 1,291.
Now let's change the year.
2016.
Enter.
And now, ALT-A-Q.
And check that out.
We have to re-enter.
It did not remember.
Like on the other sheets.
I click on AF6 data set.
Control-Home to jump up to A1.
Control-Shift right.
Down arrow.
Tab.
And there we go.
I want to make sure and select
Copy To another location.
And when I click OK,
instantly I have 2016.
So this trick works.
As long as you click in a
single cell and open it up,
you can direct
ALT-A-Q. That text box
anywhere else in this workbook
or any other workbook.
Click Escape.
Now we got to go back to five.
Because here's
one of the amazing
uses for Advanced Filter.
If I want to extract a
unique list of customers,
I could copy this column.
Paste it somewhere.
Click Remove Duplicates.
I could create a pivot
table and drag the customer
feel to the row area.
But then it would be
inside of a pivot table.
But Advanced Filter?
I can simply say,
look at this column.
Check unique list only.
And boom.
We will get our unique list.
Click in a single cell.
ALT-A-Q. And check that out.
ALT-A-Q.
Now unfortunately I already
did it on this sheet.
So it remembered the
last time I did it.
Most of the times when you are
doing it for the first time,
it will automatically
get the whole sheet.
So you have to be sure
and redirect List Range
if you're extracting only
a single column or whatever
number of columns.
So I'm going to click
on the top cell.
Control-Shift.
Down arrow.
Tab.
Now I wan to say Copy
To another location.
There is no criteria range.
Although for-- I'm going
to scroll up here--
for unique records only,
you could have criteria.
But we do want to check
unique records only, and then
click Copy To.
And I think I'm
going to click in I4.
Click OK.
And just like that,
we have a unique list.
Now I'm going to do
another unique list.
ALT-A-Q. And remember Copy To.
And we have to make sure
that we get the right column
or series of columns we want.
Control-Shift.
Down arrow.
Tab.
We don't have any criteria.
I definitely want to
change this to like K4.
And check unique records only.
When I click OK, there's the
unique records for the sales
rep and customer column.
Now we've got to go look
at our last amazing example
for Advanced Filter.
And I actually want to go look
at the end result first, which
is in a different workbook.
My goal is to create
this report right here.
It's only a certain number of
countries we're interested in
and a certain number of years.
And on the inside
are GDP numbers.
Now we're going to actually have
to extract all of these records
from a huge data set.
So I'm going to go back over
to our business 2/18 video
19 start file.
And I want to go
over to AF7 data set.
Here's what we're trying to do.
We have country indicator name.
And then from 1970 Control right
arrow, all the way to 2013.
And all of these
numbers are GDP numbers.
Actually they're not just GDP.
They're a bunch of
different numbers.
It tells you for each
country what the metric is
or whatever they're measuring.
So for Afghanistan, here
are all of the labels
for all the numbers for
each one of the years.
And our goal is to only
get Gross Domestic Product.
So there's an example of one.
And down here in
Albania, there's
another Gross Domestic Product.
So if I Control-down arrow,
there's like 3,000 records
here.
And so for this
column, we're only
interested in Gross
Domestic Product, GDP.
Not only that, but we've
been given a list up here.
We want Argentina, Belgium,
Brazil, Canada, Finland,
France.
And so I'm like 25
different countries.
So that would come
from this column.
Now we could do this
with Power Query.
And in fact we would do it
if we needed it to refresh.
But Power Query would
be much more complicated
than using Advanced Filter.
Not only that, but
if we did filter,
we would have to, from
this list right here,
click the drop down arrow
and check and uncheck
to get all the right
countries which
would be kind of tricky there.
Not only that, but our
third criteria is we
only want the columns.
And if I scroll over,
we only want-- whoa,
there's a bunch of them.
2000 to 2010.
So how in the world are
we going to do that?
Well, I'm going
to do it two ways.
And we're going to start
with a helper column.
Remember, we need only GDP.
So I'm going to start
with a helper column one.
And I'm just going to say hey.
Is this cell two
cells to my left--
relative cell reference-- are
you equal to--and watch this.
I'm going to cheat.
I don't want to type it out.
Gross Domestic Product.
I'm going to double click it
and hit the F9 key to hard code
that in.
So there's our
True-False formula.
Control-Enter.
Double click.
And send it down.
So you can see we
get a True there
because this in essence line
has Gross Domestic Product.
Now how in the
world are we going
to check if the country
matches this list over here?
And this list is
in a single cell.
Well notice I used the phrase,
check if this country is
in this list.
And we've done this a bunch
of times in this class.
Now if it was actually
in a list of cells,
we would use the match function.
But instead I'm going to check
if this smaller bit of text
is within this larger bit of
text using the Search function.
And so we're going to
use the Search function.
Search.
And I'm going to find what text?
Relative cell
reference, comma within.
Boom.
There is is right there.
If it finds it, remember Search
will tell us the position
that it finds it in.
So if it finds Argentina,
it'll be like one.
If it finds Belgium, it'll be
like 10 or whatever that is.
But I need to lock
this all the way down.
This is amazing.
Control-Enter.
Double click and send it down.
Notice we get a bunch of values
because none of those countries
are there.
But when we get down to
Argentina, there it is.
Search is saying I found
Argentina in that larger
text up there in
the first position,
meaning the first character.
If we scroll down,
there's Belgium.
So I found Belgium in the 12th.
Control-up arrow.
Or what are we interested in?
We're interested in the number.
So I say is number
close parentheses.
Control.
Enter.
Double click.
And send it down.
Now watch this.
And there is.
GDP.
Only when we get two Trues in
a row do we want this record.
Meaning we need these numbers
for the columns 2001 to 2010.
So watch this.
Since I don't want
multiple columns,
I'm going to create yet
a third column here.
And this will be
the one that we use.
I need to make sure this
is True and this is True.
Now I'm going to copy
this in Edit mode.
Control-CC to copy it and
open up the clipboard.
If your clipboard doesn't
open, then click right there.
So I have that little
bit right there.
Escape.
And now I'm going to
come and copy this bit.
Control-C.
So I've loaded up the two bits.
And now I can use what?
The And function.
So And logical test one.
There it is.
Logical one.
Come to the end.
Comma logical two.
It's going to be this is number.
Close parentheses.
That right there
is the formula that
will give us the True meaning
we want this particular record.
So when I double click
and send it down,
now we have a helper column.
And if I scroll all the
way down, there it is.
The criteria we need
for this helper column
is going to be the word True.
Now notice we're going to need
this helper column field name.
Because that'll be our only
criteria for this entire column
here.
Control-C.
And now I'm going to go
over to AFC helper column.
Notice I already
started to set this up.
I'm going to Control-V. There
is the field name and the data
set, the criteria for
extracting the record is True.
And watch this.
We're going to need
Country, right?
So back over here
we're going to need
to the actual column, Country.
And then those
years further over.
So when I click on
AF7 HC, there it is.
This would be the criteria
for which columns to extract.
Now this is on a
different sheet.
I'm going to click
in an empty cell.
ALT-A-Q. Copy To
another location.
List range.
I'm clicking back
on AF7 data set.
Clicking on helper three.
Control-Shift right arrow.
Control-Shift down arrow.
Tab.
So there we got that
from that other sheet.
Criteria.
There it is.
Helper three.
And True.
Tab.
Copy To.
I want to tell only
certain columns.
So I highlight all
the way to 2010.
And you're not going
to believe this.
When I click OK, there it is.
From that huge data set.
Now let's do some
formatting to make
this an awesome
professional report.
I'm first going to
highlight all these numbers.
Control-1.
And I want to go to Number.
Use a comma separator.
I do not need decimals.
Not only that, I'm going to
amend this built-in number
formatting by coming
down to Custom.
And if I come to
the end here, we
learned this all the way
back in video number one.
We can in essence hack
off thousands or millions
by putting an extra comma.
If I put one comma, it cuts it
up by three zeroes or 1,000.
If I type a second
comma, that means
I'm going to only show
up to the million.
This is number formatting.
It's not rounding the formula.
When I click OK,
there are our numbers.
Now I'm going to
highlight the columns
and try and change all
of the column widths
with one fell swoop.
I'm going to click and drag.
So there we go.
Now I'd like to highlight
alternative columns here.
So I'm using the Control
key to highlight columns
not next to each other.
I want to add formatting
for alternating columns.
This'll help make
it easier to read.
Now I'm going to go up to home.
Click the drop down.
I think it's that
one right there.
Now I want to turn
off the grid lines.
So go up to View.
Uncheck grid lines.
And there is our report.
We could highlight
this and print it out.
In fact, let's do just that.
I'm going to use Page Layout.
There's the dialogue
launcher for Page Setup.
I'm going to use the
keyboard ALT-PSP.
By the way that
works in Word also.
All right.
I definitely want it landscape.
Fit to one page wide
by one page tall.
Margins I'm going to center
at horizontally only.
Header and Footer.
I can add whatever
Header and Footer.
I'm going to add page
one of question mark.
Even though I only
have one page,
this is going to be with
a bunch of other handouts.
And so I want them to
know that this is 1 of 1.
And then over on Sheet,
I want to limit the area
because I don't want any of
this Advanced Filter stuff
to print out.
So in Print Area, I'm
going to highlight.
When I click OK and
Control-P, check that out.
There is our report
based on Advanced Filter.
Now I want you to
remember something.
This was based on a formal
and a helper column.
If we go over to
AF7 Control-Home
to jump to the top, I
want you to notice that we
did add this helper column.
Most the time we can add a
helper column to a data set.
But sometimes we cannot mess
with the data set at all.
So Advanced Filter
has no problem
as long as you're able to
create a helper column formula.
If we use this exact
formula instead of the word
True over in our criteria
area, and we actually
use the formula,
Advanced Filter will
see that there's a formula
there and in memory copy it
down as if there
were a helper column.
So I'm going to do a trick.
I actually am going to
need sheet references.
If I copy and paste this,
these would not change.
So I'm going to cut it with
Control-X. When you Control-X
a formula that's
pointing at certain cells
on this sheet,
when you paste it,
it will create sheet references.
So now I'm going to go
over to AF7 with a formula.
And in the cell
where the word True
was for our last
helper column example,
I'm going to Control-V F2.
And notice it got all of
the right sheet references.
Now why is it false?
Because if we go
back over to our data
set-- and now I'm going to
copy this one up to replace it.
Because of course it's
looking at only the first one
which has False.
Now let's go try this.
This is truly remarkable.
And you could put whatever word
column name or any text or you
can keep it empty.
We are still going to have to
have two cells for Advanced
Filter to know what to do.
I'm going to type formula
criteria, colon, and Enter.
Now I'm going to click in
an empty cell ALT-A-Q. Copy
To another location.
The list range.
It's on the other sheet.
So I click AF7.
And I'm not going to highlight
these helper columns to prove
that we don't need those.
And actually I'll delete
those and show you
that it still works.
But I'm going to
click in the top cell.
Control-Shift right arrow.
Control.
Down arrow.
Tab.
Criteria range.
There it is.
Advanced Filter is
actually smart enough
to see that there
is a formula there.
And in memory it will
take it and copy it
down as if it was
next to the data set.
Tab.
Copy To.
This is in essence where we
tell Advanced Filter which
columns we want.
And now when I click
OK, same exact numbers
based on formula criteria.
Now I should have deleted
the actual helper columns
first to prove it to you.
But if I delete them now,
Control-Shift down arrow,
delete.
It all works perfect.
Now I'm going to Control-Z
to leave that as a trail.
Wow.
Advanced filter.
That is pretty amazing.
We used Advanced Filter to
do quite complex criteria
with either a helper column
over in our data set and then
the criteria True.
Or over here we actually created
the formula and Advanced Filter
in memory copied it down as
if it was a helper column.
Back on AF6, we saw
how to create formulas
in the criteria area.
But the differences we
had are field names here.
So it took the straight
criteria in the cell
and we extracted by year.
Over on AF5, we saw how to
extract unique records only.
On AF4, we did complex criteria
with And and Or criteria.
On AF3, we did
Contains criteria.
AF2, we did Or criteria.
And AF1, one we
did And criteria.
Advanced Filter.
Pretty amazing.
Now in our next
few videos, we'll
talk more about Power Query.
All right?
We'll see you next video.