All language subtitles for 4. Using the Advanced Filter

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French Download
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranî)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt-PT Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu
Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:05,050 --> 00:00:08,290 So now we've had a thorough look at sorting our data. 2 00:00:08,370 --> 00:00:15,370 It's time to move on to filtering now most of us are familiar with using the dropdown arrows to filter 3 00:00:15,370 --> 00:00:19,450 our datasets and extract exactly the information that we're interested in. 4 00:00:19,690 --> 00:00:23,770 But did you know that there is also an advanced filter option? 5 00:00:24,460 --> 00:00:30,100 And in my opinion, this is one of the most underrated commands in Excel because it's not something 6 00:00:30,100 --> 00:00:31,930 I see talked about very often. 7 00:00:32,770 --> 00:00:39,070 So let's take a look at how the advanced filter works because it has many uses now in this first example. 8 00:00:39,100 --> 00:00:45,070 I'm going to show you how you can use the advanced filter to extract a unique list of values from your 9 00:00:45,070 --> 00:00:45,670 dataset. 10 00:00:46,540 --> 00:00:50,640 So if we take a look at the table on the left hand side, I have a list of sales reps. 11 00:00:50,650 --> 00:00:55,390 I have the region that they belong to and then their sales information. 12 00:00:55,870 --> 00:01:02,050 Now notice here in the sales rep column, I have the sales rep names repeated over and over again. 13 00:01:02,380 --> 00:01:07,660 So technically, there's only roughly five sales reps that just repeated throughout this data. 14 00:01:08,470 --> 00:01:14,380 Now, maybe I want to extract from this column just a unique list of all of the sales reps. 15 00:01:14,920 --> 00:01:18,160 And I find this is something I have to do all the time. 16 00:01:18,460 --> 00:01:22,450 Now we can extract two unique list using Excel's advanced filter. 17 00:01:22,750 --> 00:01:25,180 So let's take a look at that, first of all. 18 00:01:25,990 --> 00:01:32,530 So if I click somewhere over here, I'm going to say unique list of sales reps. 19 00:01:33,820 --> 00:01:38,470 And then underneath here, I just want the five or so names of the sales reps. 20 00:01:39,310 --> 00:01:45,160 So what we can do here is jump up to the data tab and in the sort of filter group, we have an advanced 21 00:01:45,160 --> 00:01:48,310 filter option and you can see here with the screen tab. 22 00:01:48,310 --> 00:01:52,600 It says it contains options for filtering using complex criteria. 23 00:01:53,500 --> 00:01:55,540 So let's click on Advanced. 24 00:01:56,020 --> 00:02:00,820 Now the thing with this advanced filter in this little pane opens up is it does hold in it the last 25 00:02:00,820 --> 00:02:01,990 criteria you used. 26 00:02:02,440 --> 00:02:07,000 So we're going to delete that out in a moment, but the first thing we need to specify is what action 27 00:02:07,000 --> 00:02:07,930 we want to perform. 28 00:02:08,680 --> 00:02:13,360 Now do I want to filter this list in place or do I want to copy it to another location? 29 00:02:13,540 --> 00:02:19,270 Well, I want to extract a unique list of the sales rep names to a different cell. 30 00:02:19,840 --> 00:02:22,150 So we want to copy to another location. 31 00:02:22,360 --> 00:02:26,680 And then we have three blank fields to complete. 32 00:02:27,430 --> 00:02:31,630 Now, when you're extracting a unique list of records, we only actually need to complete two of these. 33 00:02:32,200 --> 00:02:34,570 And the first one is the list range. 34 00:02:34,960 --> 00:02:37,300 So where are the values that we want to extract? 35 00:02:37,930 --> 00:02:40,690 Well, they're in the sales rep column. 36 00:02:40,990 --> 00:02:46,570 Now for this one, we don't need to specify any criteria, but we do need to let excel know where we 37 00:02:46,570 --> 00:02:48,400 want to copy these unique values. 38 00:02:49,030 --> 00:02:53,200 And I want to copy them to this sale just here f 11. 39 00:02:53,470 --> 00:02:59,620 And the most important thing is to make sure you select unique records only when we click on. 40 00:02:59,980 --> 00:03:00,670 OK. 41 00:03:00,910 --> 00:03:01,780 Take a look at that. 42 00:03:01,780 --> 00:03:04,030 I now have my unique list. 43 00:03:04,330 --> 00:03:08,110 Now there is a way to do the exact same thing by using a formula. 44 00:03:08,590 --> 00:03:14,590 And this is again one of those new formulas in Excel 2021, and I'm going to show you how that works 45 00:03:14,590 --> 00:03:15,640 in the next lesson. 46 00:03:16,210 --> 00:03:21,580 But for the time being, you can also use your advanced filter to extract a unique list of values. 47 00:03:21,790 --> 00:03:24,190 Now what else can we use this advanced filter for? 48 00:03:24,190 --> 00:03:27,070 Because that is by no means all we can do with this? 49 00:03:27,730 --> 00:03:31,990 Well, let's jump across to our next worksheet where we have the same data. 50 00:03:32,170 --> 00:03:39,430 We can also use the advanced filter to extract values based on specific pieces of criteria. 51 00:03:39,700 --> 00:03:44,050 And the way this works is you have to have your criteria listed out in your spreadsheet. 52 00:03:44,890 --> 00:03:47,320 So you can see here in this little table over here. 53 00:03:48,100 --> 00:03:56,830 I want to extract from this table all of the records where the region is Asia and the sales total is 54 00:03:56,830 --> 00:03:58,570 greater than 7000. 55 00:03:59,320 --> 00:04:02,650 And we can do this again using our advanced filter. 56 00:04:03,040 --> 00:04:04,990 So I'm going to click somewhere underneath. 57 00:04:05,380 --> 00:04:07,150 Let's click on Advanced. 58 00:04:08,260 --> 00:04:10,600 Now I want to copy to another location. 59 00:04:10,690 --> 00:04:13,080 Let's make sure we select that and live action. 60 00:04:13,090 --> 00:04:16,060 And then I'm just going to delete out all of this old criteria. 61 00:04:17,200 --> 00:04:21,160 Now for this one, we are going to utilize all three of these boxes. 62 00:04:21,430 --> 00:04:25,690 Now, the first thing we want to select is the information we want to extract. 63 00:04:26,320 --> 00:04:31,390 While I want to extract the date, the sales rep, the region and the sales information. 64 00:04:31,390 --> 00:04:33,460 So basically everything in my table. 65 00:04:34,120 --> 00:04:38,290 So my list range is going to be my entire table. 66 00:04:39,040 --> 00:04:46,140 I then need to specify my criteria range and I have my criteria listed in this small table just above. 67 00:04:46,180 --> 00:04:49,390 So I need to select the entire little table. 68 00:04:50,500 --> 00:04:54,220 Then I can specify where I want to copy these results to. 69 00:04:54,910 --> 00:04:57,730 So let's click on Sell F nine. 70 00:04:58,030 --> 00:05:02,680 I don't need to select unique records this time because that's not what we're doing. 71 00:05:02,890 --> 00:05:04,360 All I need to do here is click. 72 00:05:04,430 --> 00:05:04,670 On. 73 00:05:04,940 --> 00:05:05,510 OK. 74 00:05:06,080 --> 00:05:11,900 And take a look at that, I now have a filtered list that showing me just the records where the region 75 00:05:11,900 --> 00:05:16,310 is Asia and the sales are over 7000. 76 00:05:17,030 --> 00:05:25,310 So effectively what we've done here is a filter using and criteria filter for Asia and sales greater 77 00:05:25,310 --> 00:05:26,300 than 7000. 78 00:05:26,690 --> 00:05:29,990 Now I can also filter using or criteria. 79 00:05:30,530 --> 00:05:32,990 So let's take a look at what that would look like as well. 80 00:05:33,560 --> 00:05:36,900 So I'm just going to clear everything from here. 81 00:05:37,580 --> 00:05:40,070 And let's change our filter criteria. 82 00:05:40,850 --> 00:05:45,920 Now, if you want to do in all your criteria, needs to be listed one above the other. 83 00:05:46,640 --> 00:05:55,520 So maybe I want to filter for records where the sales rep is Ben or Deb, and let's just keep our formatting 84 00:05:55,520 --> 00:05:56,240 consistent. 85 00:05:56,540 --> 00:05:57,840 Let's just apply that. 86 00:05:58,430 --> 00:06:01,850 And let's also apply that like so. 87 00:06:02,210 --> 00:06:06,410 So now I have these in here, it's going to do and or filter. 88 00:06:07,220 --> 00:06:11,510 So let's go back to our data, tap into advance. 89 00:06:11,510 --> 00:06:14,090 And we're basically going to do a very similar thing. 90 00:06:14,090 --> 00:06:18,230 We're going to copy to another location and let's remove all of these. 91 00:06:18,230 --> 00:06:24,080 I like to start from a clean slate, a list range where we want to pull back all of the information, 92 00:06:24,080 --> 00:06:25,760 so we select the entire table. 93 00:06:26,540 --> 00:06:34,970 Our criteria range is this table just here and we want to copy to this cell and click on OK. 94 00:06:35,210 --> 00:06:41,900 And now I should find that my filtered results are only for the sales reps, Ben or Deb. 95 00:06:42,260 --> 00:06:44,990 So that is how the advanced filter works. 96 00:06:45,260 --> 00:06:50,510 It's great for extracting unique lists of information, but it's also great for filtering data using 97 00:06:50,510 --> 00:06:52,040 more complex criteria. 9575

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.