Tad from Springfield IL asks: How can I filter to 27 specific invoice numbers?
With a large data set, scrolling to find the 27 invoices will not be fun.
Searching for each invoice and remembering to click Add to Selection will not be fun.
Instead - use the Advanced Filter
Set up a criteria range… First cell contains the Invoice heading. List the invoices vertically below.
Choose one cell in the data set. Data, Advanced Filter
Filter in Place
Use a Criteria Range
Select the criteria range including the heading
Click OK
With a large data set, scrolling to find the 27 invoices will not be fun.
Searching for each invoice and remembering to click Add to Selection will not be fun.
Instead - use the Advanced Filter
Set up a criteria range… First cell contains the Invoice heading. List the invoices vertically below.
Choose one cell in the data set. Data, Advanced Filter
Filter in Place
Use a Criteria Range
Select the criteria range including the heading
Click OK
Transcript of the video:
Learn Excel form MrExcel Podcast, Episode 2175: How to Filter to 27 Invoices.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question from Tad.
I ran into Tad in my Springfield, Illinois, Power Excel seminar, and he says, "I have a large data set here--" how many rows do we have, 15,000 rows-- "and I need to filter to 27 specific invoice numbers," and that is tedious.
Boy, tedious it is.
I'm going to speed up here, and show one way that you could do this.
Then we'll come back and show the faster way to do this.
Alright, geez.
Clearly, that method is never going to work-- it's going to be too, too tedious-- let's just clear that filter and actually turn the filter off.
What we're going to do, instead of going through one by one by one and choosing those or searching for them, either way we're going to set up something called an Advanced Filter.
An advanced Filter has a data set-- this will be the data set here-- and then, optionally, it has a Criteria Range.
And the Criteria Range has to have a heading from the original data set.
So I take that Invoice heading from A1, and I paste it above my list of Invoices.
Now watch how simple this is going to be: Come here to Data, and then Filter-- nope, not Filter, Advanced-- Advanced Filter, and we're going to a filter the list in place, and my Criteria range is going to be this list over here with the Invoice heading-- make sure to include that Invoice heading-- and then click OK, and BAM!
We get all of the line items for all 27 of those invoices.
Really, really awesome.
Awesome.
Fast, fast way to go.
Alright.
Well, Filters and Advanced Filters are covered in my book, Power Excel with MrExcel, the 2017 Edition.
Click that "I" on the top right-hand corner.
Alright.
Tad from Springfield: How to filter to 27 specific invoices.
Well, with a large dataset, scrolling to find the 27 invoices will not be fun; even searching for each invoice and remembering to click Adds and Selection will not be fun.
Instead, we're going to use the Advanced Filter.
You have to set up a Criterion range, it's simple.
It's one column, first cell has to contain the heading from the original data set; and then a list of invoices; you choose one cell, one data set; Data; Advanced Filter; choose Filter in Place; specify the Criteria range; and then click OK, and you're done.
I want to thank Tad for coming to my seminar in Springfield, Illinois, and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question from Tad.
I ran into Tad in my Springfield, Illinois, Power Excel seminar, and he says, "I have a large data set here--" how many rows do we have, 15,000 rows-- "and I need to filter to 27 specific invoice numbers," and that is tedious.
Boy, tedious it is.
I'm going to speed up here, and show one way that you could do this.
Then we'll come back and show the faster way to do this.
Alright, geez.
Clearly, that method is never going to work-- it's going to be too, too tedious-- let's just clear that filter and actually turn the filter off.
What we're going to do, instead of going through one by one by one and choosing those or searching for them, either way we're going to set up something called an Advanced Filter.
An advanced Filter has a data set-- this will be the data set here-- and then, optionally, it has a Criteria Range.
And the Criteria Range has to have a heading from the original data set.
So I take that Invoice heading from A1, and I paste it above my list of Invoices.
Now watch how simple this is going to be: Come here to Data, and then Filter-- nope, not Filter, Advanced-- Advanced Filter, and we're going to a filter the list in place, and my Criteria range is going to be this list over here with the Invoice heading-- make sure to include that Invoice heading-- and then click OK, and BAM!
We get all of the line items for all 27 of those invoices.
Really, really awesome.
Awesome.
Fast, fast way to go.
Alright.
Well, Filters and Advanced Filters are covered in my book, Power Excel with MrExcel, the 2017 Edition.
Click that "I" on the top right-hand corner.
Alright.
Tad from Springfield: How to filter to 27 specific invoices.
Well, with a large dataset, scrolling to find the 27 invoices will not be fun; even searching for each invoice and remembering to click Adds and Selection will not be fun.
Instead, we're going to use the Advanced Filter.
You have to set up a Criterion range, it's simple.
It's one column, first cell has to contain the heading from the original data set; and then a list of invoices; you choose one cell, one data set; Data; Advanced Filter; choose Filter in Place; specify the Criteria range; and then click OK, and you're done.
I want to thank Tad for coming to my seminar in Springfield, Illinois, and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
