Filter by Selection

The MrExcel podcast is sponsored by "MrExcel XL", a book with 40+ Excel tips, Excel cartoons, cocktails, tweets and jokes.

Learn Excel from MrExcel podcast, episode 1975 - Filter by Selection!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. And I know a lot of you use the data filter command, but frankly, this is a slow command to use. If I needed a filter to one particular customer here, well you have to first click on data, a second click for Filter, that gives us the dropdowns. Then we open the drop-down, that's click #3, Uncheck "Select All", click #4, and then I don't know how many clicks this counts, to drag down through this long list of find what we want, let's call that click #5, click #6 for the customer, click #7 for OK.

There's a much, much faster way to go. Now, before Excel 2007 gave us a million rows, I used to be a pretty big fan of Microsoft Access, not because I was doing any database things, but because I could pull a large CSV file in maybe 80,000 rows, break it into two pieces with queries, and then take it back to excel on two different worksheets. But now you know, we have not just 65,536 rows, but 1,048,576. I don't have more data than that so I frankly, haven't used Access since Excel 2007 came along. But one of my favorite features back in Access, in the filter group was something called "Filter by Selection". And all you would have to do is just choose the cell that had what you wanted, and click Filter by Selection. And I remember, back when I started the podcast, way back in 2007, someone wrote and said: "You know, Filter by Selection so good in Access, why don't we have that in Excel?" Alright, and at that time I actually wrote a macro, and somewhere out on YouTube is the video, where I show that that Filter by Selection macro, and you know, like, the next day someone wrote in and said: "Wait a second, why did you write that macro?" In fact, it was before 2007, because we were still in Excel 2003. They said "You know Filter by Selection's in Excel 2003!" It was hidden, you couldn't find it.

Now here's how you would find it today: Now first off we're either going to add it to the ribbon, or run out to the Quick Access Toolbar, and I always add it to the Quick Access Toolbar up there, so I'm going to right-click, and say that I want to customize the Quick Access Toolbar, and they start out by showing me a list of 40 or so popular commands. But this isn't a popular command because no one knows that it's there! So we're either going to go to All Commands, or my favorite Friday afternoon activity - Commands Not in the Ribbon. Right? These are things that Microsoft is hiding from us, things that are in Excel, that they don't want us to see, maybe because they want to remove them someday. Alright, so this is great to go through here, and see if anything looks interesting that you might be able to use. Now of course you would think, that Filter by Selection is going to either be in the F's, or down in the S', like Selection, Filter by, but it's not in either of those. What we have to do is we have to go back to the old name. Remember the old name back in Excel 2003? It was called an AutoFilter, right? So, there is another icon called Filter, if we went to all commands, but we want the one called AutoFilter, we're going to add that, alright. And see, that's the problem, it's mislabeled. They call it AutoFilter classic. That's really Filter by Selection.

So here's how it works: If I want one particular customer, I click that customer, that's one click, don't even have to go to the data tab, just come up here to the Quick Access Toolbar, And BAM, there we are! It is a 2-click way to filter. If I need to filter even further all the Gizmos, choose Gizmo, click the Filter by Selection, choose Monday, Filter by Selection. Alright, and a really, really fast way to go. Now heed just one caution, of course what I would love, is if I could choose the customer, choose the day, choose the product, and then do Filter by Selection, but unfortunately that does not work. You can't do it that way, too bad. That would have been a cool trick.

Alright, so just to recap here: Filter by Selection, that great tool from Access, works in Excel. I talked about how to add an icon to the Quick Access Toolbar, and then "Commands Not in the Ribbon" contains Excel features that you won't find.

And again, a plug for the book "MrExcel XL", this is just tip #2, out of more than 40 tips in the book, click that "i" on the upper-right hand corner to buy the book! And thanks to Sam Rad on the Excel team, not for suggesting Filter by Selection for the book, but actually he's the guy that formalized Filter by Selection, made it be a real feature, so if you love this feature, tip of the hat to Sam Rad.

Thanks for stopping by, we'll see you next time for another netcast from MrExcel!