WEBVTT

1
00:00:01.800 --> 00:00:08.130
William Rosener: hello, this is Dr bill roesner welcome back and this instructional video you'll learn about the filter function in Google sheets.

2
00:00:08.580 --> 00:00:17.490
William Rosener: The Google sheets filter functions of powerful function, we can use to filter data, the Google sheets filter function or take your data set and return or show you.

3
00:00:17.820 --> 00:00:25.800
William Rosener: All of those roads or data that meet the criteria that you specify suppose we want to retrieve all the values that are above a certain threshold.

4
00:00:26.310 --> 00:00:37.170
William Rosener: Or the values that are greater than average or in this case, all the sales from a specific region, the Google sheets filter function is to do all these and more with just a single formula.

5
00:00:37.800 --> 00:00:51.960
William Rosener: let's start by taking a look at what you learn how to create and then we'll go ahead and recreated from scratch so over here we've got a name a region and a sales and let's just assume, I want to filter all the data from Canada.

6
00:00:53.040 --> 00:00:54.540
William Rosener: or Asian.

7
00:00:55.860 --> 00:01:10.530
William Rosener: or from Brazil or from the United States and so just with this one on a function, we can use to filter that data will ask the throne, an additional condition is here let's say we want to only filter out those that are above 1000 and i'll walk through that as well.

8
00:01:11.640 --> 00:01:22.680
William Rosener: So the very first step you're going to want to do is come here kind of start from scratch and we go to Google COM i'm going to press appear on the Google Apps i'm going to go to my drive.

9
00:01:24.420 --> 00:01:26.430
William Rosener: going to come up here to new.

10
00:01:28.350 --> 00:01:30.150
William Rosener: i'm gonna choose Google sheets.

11
00:01:32.010 --> 00:01:37.950
William Rosener: And there we go, so the same area that you found this video should also find.

12
00:01:40.440 --> 00:01:48.090
William Rosener: Some data so i'm just going to stop highlight that data and I had to control see to copy it and i'm going to come right up here and paste.

13
00:01:49.290 --> 00:01:52.860
William Rosener: It you want to let's go ahead and get some format this or.

14
00:01:53.880 --> 00:01:54.570
William Rosener: currency.

15
00:01:58.410 --> 00:02:01.230
William Rosener: Okay, so format number turn soon.

16
00:02:02.520 --> 00:02:11.040
William Rosener: Okay, and i'm also going to go ahead and just simply highlight these three pieces of data and i'm going to position them right there.

17
00:02:13.410 --> 00:02:22.740
William Rosener: You know, increase that just a little bit them Okay, so in order to use the filter function i'm going to specify i'm gonna come right in here and i'm the type equals.

18
00:02:23.670 --> 00:02:37.770
William Rosener: Common type filter warn us to arguments in this case here, the first one is going to be entire area or the array so i'm going to go from A to through see 11 and I can actually simply press here.

19
00:02:38.520 --> 00:02:47.100
William Rosener: Or the shift key down and press on see 11 or I can type that in and i'm going to go ahead and press a comma.

20
00:02:47.730 --> 00:03:06.720
William Rosener: So that was the area and the next one is going to be the condition and so i'm going to simply go be to through be loving this David this time i'm actually type it out V2 Roland be 11 equals in double quotes i'm going to put the United States.

21
00:03:08.850 --> 00:03:15.870
William Rosener: Okay, and just like that I can see i've got 1234 i've got them all over here.

22
00:03:17.040 --> 00:03:25.230
William Rosener: Okay, what would be nice as well, what if I want to get all the sales in Canada, Asia and so forth there it'd be nice if I could just easily toggle.

23
00:03:26.580 --> 00:03:39.510
William Rosener: Or have a pulldown menu record go from one region to the next, and to do that we're going to use a little data validation so i'm going to position my cursor and I want them to come right up here to data, let me come down to data validation.

24
00:03:41.040 --> 00:04:00.690
William Rosener: And i'm just going to go ahead and specify range and the range i'm going to specify is be to through beat 11 and I could come right up here and specify that and I could come over here, I, like the first one or the shift key down and highlight that or I can type in V2 colon be loving.

25
00:04:02.100 --> 00:04:22.200
William Rosener: and save so now what i've got is over here, I have got my options i've got United States, Canada, Asia and Brazil, so now taking a look, when we come right back here what you're going to find is that I should have mentioned this earlier if there was some data like.

26
00:04:23.460 --> 00:04:40.830
William Rosener: For example, on row five I probably would have got an error message, because it needs to expand below there, so now, rather than going United States i'm going to simply type in I one that's going to be right here, and then simply hit the enter key.

27
00:04:42.060 --> 00:04:49.230
William Rosener: OK, so now it's going to be taken a look it's looking at all of this entire area and as i'm going to be taking a look or be two.

28
00:04:49.860 --> 00:04:59.400
William Rosener: Through be loving the region is equal to one well I won't is currently United States but if I come up here and change that to Canada, we can see quickly, it does.

29
00:05:00.900 --> 00:05:11.520
William Rosener: it's pulling the information from that particular region there okay one last thing here, what if we wanted to only an extract or find those values above a certain amount.

30
00:05:11.970 --> 00:05:22.050
William Rosener: On this case I could come right up here and I could have a condition and and to do that i'm going to first of all start off with the parentheses over here going to put a star.

31
00:05:23.220 --> 00:05:37.320
William Rosener: And i'm gonna put my a new condition can be in within parentheses, and actually to close my privacy that starts the whole filter so in this case i'm gonna take a look at see to colon see 11.

32
00:05:38.700 --> 00:05:43.380
William Rosener: And i'm going to find those that are greater than let's say 10,000.

33
00:05:44.970 --> 00:05:52.440
William Rosener: Okay, someone to have this first one is the area, and this is my condition i'm going to take a look where be to her be loving.

34
00:05:52.860 --> 00:06:05.550
William Rosener: is equal to escape the United States or I can I can change it then i'm going to have a separate condition that's using the star were see to through see loving is greater than 10,000.

35
00:06:06.750 --> 00:06:16.350
William Rosener: So just like that i'm now I can go from one to the other, let me take a look at Canada, we can see there's actually two for Canada one preteen thousand.

36
00:06:16.830 --> 00:06:36.120
William Rosener: In sales and another 142 7000 but notice i'm only displaying those were the sales are above 1000 10,000 you know, hopefully in a bit so i'll tell you i'll me walk through this finish this process here at some point, I would come up here to file or two i'm just come right up here.

37
00:06:37.440 --> 00:06:39.090
William Rosener: I could call this assignment.

38
00:06:40.350 --> 00:06:41.070
William Rosener: six.

39
00:06:45.210 --> 00:06:54.870
William Rosener: And I think it automatically saves but what you're going to want to do is also go ahead and, as I wish the number four is you're going to want to make sure to share that some.

40
00:06:55.320 --> 00:07:07.590
William Rosener: I typically when I do this I log in as guru COM what you're going to want to do is you're going to want to share that right here with roesner at initial k.edu.

41
00:07:08.910 --> 00:07:15.000
William Rosener: And i'm not going to send it to you guys went to press this button go ahead press the send button there someone to receive an email message.

42
00:07:15.390 --> 00:07:20.520
William Rosener: Then what you're going to do there's gonna be another video that walks you through taking this link.

43
00:07:21.210 --> 00:07:33.660
William Rosener: which I came right up here to the share copy the link, and that is what you're going to place on your website so I can quickly get to you, because otherwise we get an email i'll make an email messages from you all semester long.

44
00:07:34.320 --> 00:07:43.080
William Rosener: And I don't want to, I want to go to do the grading typically like in in certain chunks on a certain Monday i'll start doing the grading and I don't want to have to be going through.

45
00:07:43.710 --> 00:07:51.660
William Rosener: email, for the last month in order to find those i'm going to go ahead, have you take that link and place that on your website and, again, there should be a video to do that.

46
00:07:52.740 --> 00:07:58.860
William Rosener: Often, this video you aren't how easy it is to use the filter function within Google sheets or catch the next video thanks bye.