Today's video is about a hidden feature in Excel's advanced filter
that I never knew about. I found out about this last week. It came packaged nicely as an
email and video from Bryon. Now I know Bryon quite well,
so when I read this sentence... (light upbeat music) I knew there's something good in there. It was better than I thought. (happy music) Of course we're going
to share it with you. What I want you to do is this, watch this super-short
video, and let me know in the comments below if
you knew advanced filter could do this or not. Type a yes or type a
no, if you didn't know. Let's jump in. First off, let's refresh our memory on what the advanced filter feature does, and then I'm going to show you the trick. So here, I have a small data set that goes over a hundred rows. I collect information
on company, customer, article, quantity, and sales in USD. What I want to do is filter this data set based on specific criteria, and I want to copy it to
another tab, the results tab. The criteria that I want
to use in this example is that article description
should include the word laptop, and that dates should be greater
than or equal to July 1st. Since I'm going to be using
advanced filter for this, I'm going to add my criteria
separately on the side here. Now you might want to
add it to a separate tab, that you have a query tab, to keep that separate to your data set, but for the purpose of keeping
things simple in this video, I'm going to add them right here. To make sure I have the
exact same spelling, I'm going to copy and
paste my criteria here. So the first one was that date should be greater or equal to July 1st. My second criteria is
that article description should include the word laptop. So I'm going to use wild cards here, start off with the asterisk
sign, type in laptop, and add the asterisk sign after that. Now in this data set, I
think laptop always occurs at the beginning of the
article description, so in theory, I don't need
the asterisk sign in front, but just in case I have an
article that has the word laptop in the middle or in the end, I'm going to add the asterisk sign to make sure those lines are
also included in my results. Okay, and if you have more
criteria you want to add, just copy the header, put it on the side, and add in your criteria here. Since I want to have my
results copied to another tab, I need to start from that tab. Okay, so always start
using advanced filter from the place you want
to have your results in. Then go to the data
tab, click on advanced. For action, we want to
copy to another location, list range is our original data set. Make sure you also include the headers, then Ctrl + Shift + Down to
include the whole data set. Criteria range is the
criteria that we just defined. It's also sitting on the
data tab, right here on top. Make sure you include the headers. Where do we want to copy it to? To the results tab in cell A1. And click on OK, and
that's my filtered list. (sighs) If you've been using
advanced filter before, this is not new for you. Now here's what you might not have known, and it's something that
I didn't know before. I always assumed that every
time you use advanced filter, and you're copying your original data set to another location, you
copy every single header. Basically, you copy the entire data set, you just filter it based on
the criteria that we want, just like we did here. But what if we didn't want
the entire data set back? What if we just wanted specific columns, and columns that didn't
even include our criteria? So let's say we just wanted to have the sales document number back, and the quantity and sales USD. So I'm just going to copy
the headers that I want back and paste them here. Now here's what you need to do. Go back to advanced
filter, so data, advanced. Repeat the same steps that we did before, so copy to another location,
list range is the same, that's our original data
set, the criteria range, nothing changes there, that's
also the original criteria that we had defined here. Now this is where we
have a slight difference. Don't click on an empty cell, but instead highlight the
headers that you want back, and Excel filters the data set, but only returns the
headers that you want. (magical harp flourish) And that's the filter trick
that I discovered from Brian. Now let's just cross-check the data and compare it to our
original filtered list just to make sure it's identical. These are the exact same document dates, same quantities, same USD,
except we don't need to get all the results returned, we
just get back what we want. Many thanks to Bryon for sharing this hidden advanced
filter option with us. Hit that thumbs up if you enjoyed it. If you want to learn more
about Excel's filter feature, check out the links below. Don't forget to subscribe to get updates and I'll see you in the next video. (upbeat music)
