0613 Advanced Filter - Differential Criteria

Advanced Excel Crash Course Section 6: Sort and Filter
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Hello, as a continuation of the discussion on Advanced Filter, here comes another video. Now, let me show you from scratch how a criteria can be built all it's very simple. What I need to do is I need to pick up let's say a city if I want certain cities specifically, and I pick up Kolkata now I would want that within Kolkata only the ones which are more than hundred thousand dollars should appear in my final output. So I pick up the hitting, copy pasting is the best method because I do not risk writing the incorrect spelling of the header. And I'm going to put a greater than sign or in fact, greater than or equal to and then right maybe one lakh Yeah, that's one 10th of 1,000,001 lakh and now let me proceed the Advanced Filter. So I go to data, I go to advanced.

In here, I first choose Copy to another location, and within which I'm going to delete all the options so that we can start from scratch. Yeah, give me a minute. Now, I place my cursor under list range and I pick up the entire data starting from the header including the header. So shift control right shift CTRL down. Once having done so, I press Ctrl backspace. Now please note Ctrl backspace is not important, it's just to ensure that having the data selected and keeping it So, you are able to go back to the header and see where you began.

So, once I choose in the list range, I go to criteria range and this time I pick up the entire portion. and thereafter I go to copy to and pick up the cell destination cell where the output should begin. So as I press OK, I should be getting only Kolkata B's climbs who are exceeding $100,000 in number. So then, that is how you make an Advanced Filter output. Now, let me do one thing, Let me press Control Zed when once you've applied Advanced Filter, it doesn't Let you press controls it anyways, what I'm gonna do is I'm going to copy one blank cell, choose the entire data block and simply paste it so that it supersedes all the data that we had prepared. Now, if you want multiple differential criteria, for example, you want to find out who are the clients in Kolkata city which are located in park Plaza and in Gurgaon, which are the clients which are located in the building called Shanthi mckeithen.

So this is like one line at a time it reads one line at a time Kolkata Park Plaza, Koreatown Shanti Nikitin similarly, if you had placed your criterias like these under location, Park Plaza more than 70,000 and the location shanton, even more than 50,000 which means this is a differential multiple filter, like me, in fact, use the case three to populate the output and see what we get. So I go to advance. I go to copy to another location. Just for our Our cosmic purpose, our our learning purpose, I'm reflecting the entire data set. So shift control, right shift control down. And what do we need to press to go back to the header?

Huh? Yes, Ctrl backspace key. In the criteria range, I'm going to delete the existing one and choose the case three. So I'll have to move to the right hand side. Yep, here goes, I choose it. So please ensure that you are taking the header in both the list range as well as the criteria range.

And when I go to copy to, ah, I may have made a mistake here. And this is quite natural. Since you're using it for the first time in the criteria range, you must pick up this entire group of cells. And once you've ensured that you've picked up the right range of cells, then and then only you go to copy to and it primarily has to be just one cell from where the data should begin as I press OK. Ideally, I should be getting in few moments per class, which is more than 70,000 and shanty Nikitin, specifically the ones which is more than $50,000 so we'll see more variations of Advanced Filter as we proceed. Till then practice these three criterias Thank you for watching so far

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.