MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Advanced Filter


October 27, 2017 - by Bill Jelen

Advanced Filter

Using Advanced Filter in Excel to solve Mort's problem. Although regular filters have gotten more powerful, there are still times that the Advanced Filter can do some tricks that others can not.


Watch Video

  • The Advanced Filter is more "advanced" than regular filter because:
  • 1) It can copy to a new range
  • 2) You can build more complex criteria such as Field 1 = A or Field 2 = A
  • 3) It is fast
  • Mort is trying to process 100K rows in VBA by looping through records or using an array
  • It will always be faster to use built-in Excel features than writing your own code.
  • You need an Input range, and then a Criteria Range and/or an Output Range
  • For the input range: single row of headings above the data
  • Add a temporary row for headings
  • For the output range: a row of headings for the columns you want to extract
  • For the criteria range: headings in row 1, values starting in row 2
  • Complication: Older versions of Excel would not allow the output range to be on another sheet
  • If you are writing a macro that might be run in 2003, use a named range for input range to circumvent

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2060 Excel advanced filter hey
  • welcome back to the MrExcel NetCast
  • I'm Bill Jelen today's question sent in
  • by Mort Mort it has a hundred thousand
  • rows of data and he's interested in
  • column a B and D where column C matches
  • some particular year so what's the
  • person to enter a year and then get
  • columns a B and D and more to have some
  • vba where is using arrays to do this and
  • I said wait a second you know the
  • advanced filter would do this a lot
  • better all right and now just to review
  • I went back I looked back through my
  • videos I haven't covered advanced filter
  • in a long time so we should talk about
  • this advanced filter requires an input
  • range and then at least one of these a
  • criteria range or an output range
  • although today we're going to use both
  • of those all right so the input range is
  • your data and you have to have headings
  • about the data so Mort does not have
  • headings and so I'm going to temporarily
  • insert a row up here and just do like
  • field one more knows what his data is
  • and so he could put real headings up
  • there and we're not using anything's
  • called this data over in columns III 0
  • so I don't have to add headings there
  • and so now a 1 through D hundred
  • thousand becomes my input range and then
  • the output range in the criteria range
  • the output range is just a list of the
  • headings that you want so I'm going to
  • put the output range here and we don't
  • need field three so i'll just take that
  • off to the side so now this range right
  • near a1 through c 1 becomes my output
  • range that tells Excel which feels I
  • want from the input range and they could
  • be in a different order if you wanted to
  • reorder the things like if I want it
  • filled for firstnet field one and then
  • feel too and again these would be real
  • headings like invoice number i just
  • don't know what mort's data looks like
  • and then the criteria range criteria
  • range is a heading and what value you
  • one so let's say I was trying to get
  • anything in the year 2014 this becomes
  • the criteria range like that all right
  • now just a word of caution here i'm in
  • Excel 2016 and it's possible to do an
  • advanced filter between two sheets in
  • Excel 2016 but if you go way back and I
  • don't remember what way back is maybe
  • 2003 I'm
  • at some point in the past it used to be
  • that you couldn't do an advanced filter
  • from one sheet to another sheet so you
  • would have to come here and name your
  • input range you would have to create a
  • name hear my name or something like that
  • all right and that would be the way that
  • you would be able to pull this off all
  • right not necessarily an Excel 2016 but
  • again again i'm not sure if more it's
  • going to be running this in older
  • versions of the day all right so
  • backyard data we go to advanced filter
  • all right and we're going to copy to
  • another location that enables our output
  • range there all right so the list right
  • where is the data because I'm in Excel
  • 2016 I'm going to go point to the data
  • instead of using the name range so
  • that's my input range the criteria range
  • is that those cells right there and then
  • where we got a output too it's just
  • going to be these three cells there and
  • then we click OK all right and bam
  • that's how fast fast it is and what if
  • we wanted a different year if we wanted
  • a different year we would delete the
  • results put in 2015 and then do an
  • advanced filter again copy tune of the
  • location click OK and there's all the
  • 2015 records lightning fast all right
  • now while I'm a fan of advanced filter
  • in regular Excel I was a massive fan of
  • advanced filter in vba all right because
  • vba makes it Vance filter really really
  • it really simple all right so we are
  • going to write some code here for Mort
  • assuming that mort's data has no
  • headings we're going to temporarily add
  • the headings alright so I'll switch over
  • to vba alt f 11 and we're going to run
  • this from the worksheet that has the
  • data so dim WS is work sheet set w s
  • equal to active sheet and then insert
  • row 1 and just add some headings a be
  • year and d figure out how many rows of
  • data we have today and then from
  • starting from cell a1 going out for
  • columns down to the final row name that
  • to be input range alright and then this
  • is
  • actually mort's code right here where he
  • asked for the input box gets the year
  • they want and then he asks what year or
  • what they want to name the new sheet
  • right so it's going to actually insert a
  • sheet on the fly and then I dimension a
  • new sheet wsn as the active sheet so i
  • know that ws is the original sheet wsn
  • is the new sheet that was just added on
  • the new sheet put the criteria range so
  • i don't call me there's the heading that
  • matches this heading here and then
  • whichever answer they gave us goes in e
  • to the output range is going to be my
  • other three headings a B and D and again
  • if you or Mort changes these two real
  • headings which is probably a better
  • thing to do than a BD you'd also change
  • these so real headings all right so all
  • of this is just a little bit of pre work
  • here this this one awesome line of code
  • will do the entire advanced filter so
  • from the input range we do an advanced
  • filter we're going to copy that's our
  • choice filter in place or copy the
  • criteria range is e1 to e2 the copy to
  • range is a to c unique values now we
  • want all of the values all right that
  • one line of code there does all of the
  • magic of looping through all the records
  • or replaces looping through all the
  • records or doing the arrays and then
  • we're done we will clear the criteria
  • range and then delete row one back on
  • the original worksheet okay so let's
  • switch back here to our data we will
  • make it easy to run this so insert a
  • shape and call this filter
  • home center Center larger larger larger
  • right click assign macro and assign it
  • to macro for more alright so here we go
  • we're going to do a test see we're on
  • the data sheet click the filter what
  • year during one we want 2015 what do I
  • want to call it I want to call it 2015
  • right and bam there is done that's how
  • fast that's how fast this is now I since
  • Morse original data didn't have headings
  • maybe this data shouldn't have headings
  • so let's go alt f 11 right here when I
  • clear the criteria range we will also
  • rose 1 dot delete all right so now the
  • next time we were on this it will get
  • rid of those headings and let's just
  • rather than run the whole thing quickly
  • let's take a look here with the 2014 so
  • I'll select one selling the data all to
  • f11 and I want to run just down to the
  • point where we do the advanced filter so
  • we can look and see what the whole macro
  • is doing here so we'll click run and I
  • want to get 2014 2014 all right and so
  • press f8 or about to do the advanced
  • filter we can roll back to excel here
  • and see what's happened first thing
  • that's happened how first things
  • happened is we've added a new temporary
  • row with the headings inserted this
  • worksheet built a criteria range with a
  • heading and what year they input chose
  • the fields we want to do and then back
  • in vba I'll run the next line of codes
  • that's f8 that does the advanced filter
  • right there and it's incredibly fast and
  • you'll see that that has actually now
  • brought us all the records from there
  • it's just a bit of cleanup delete this
  • delete this I'll go back to the data and
  • delete row one and we will be good to go
  • so i'll just let the rest of that run
  • remove that break point all right so
  • there's the vba for me this is I think
  • the fastest way fastest way to go all
  • right episode recap the advanced filter
  • is more advanced than the regular filter
  • because while I can copy to knit range
  • and now I didn't show it in this video
  • but you can build complex criteria where
  • field1 is equal to a or field two is
  • equal to a the regular auto filter can't
  • do that and it's fast more trying to
  • process 100,000 rows in vba by using an
  • array or by looping but it'll always be
  • faster you use Excel building features
  • and writing your own code you need to
  • define an input range criteria range
  • output range you always need an input
  • range in at least one of these although
  • today I'm using both for the input rain
  • single row of headings above the data so
  • we're going to temporary robe headings
  • for the output range the same headings
  • that you want to extract all right so
  • you know if it was a beer and d you just
  • what a B and D is the output range for
  • the criteria range headings in Row 1 so
  • this is the field I want to build a
  • criterion and this is the value i'm
  • looking for complications older versions
  • of excel will not allowed the output
  • range to be on another sheet so if
  • potentially your code will run back then
  • you want to use a named range for the
  • input range because from this sheet you
  • know the named range even though it's on
  • another sheet the sheet believes the
  • name branches on the current sheet so
  • that would allow the advanced filter to
  • work all right well there you have it
  • want to thank more you sending a
  • question why thank you for stopping by
  • we'll see you next time for another
  • NetCast from MrExcel

Download File

Download the sample file here: Podcast2060.xlsm

Title Photo: Free-Photos / Pixabay


Bill Jelen is the author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.