1 00:00:02,250 --> 00:00:08,940 Let's take a look at using Flash Fill. Flash Fill is going to be your magic tool when it comes to not just 2 00:00:08,970 --> 00:00:09,890 entering data 3 00:00:09,930 --> 00:00:17,370 but also manipulating data. You can do really complex data manipulations without you having to use alternate 4 00:00:17,370 --> 00:00:24,150 methods. Like looking for a complex formula that can do this or another type of feature or even VBA to 5 00:00:24,150 --> 00:00:25,470 get the job done. 6 00:00:25,470 --> 00:00:27,680 I've prepared a few examples. 7 00:00:27,720 --> 00:00:33,720 This is going to give you an idea of the different instances you can apply Flash Fill. In the first 8 00:00:33,720 --> 00:00:34,440 example 9 00:00:34,440 --> 00:00:40,080 I have the name of the persons. Let's say I need to split them into first name and last name. 10 00:00:40,110 --> 00:00:42,540 One option is to use Flash Fill. 11 00:00:42,660 --> 00:00:47,760 All I have to do is type in the first name of the person, drag this down. 12 00:00:48,180 --> 00:00:49,440 What's going to happen by default. 13 00:00:49,440 --> 00:00:53,730 We saw in the previous lecture we're just going to be copying that name. 14 00:00:53,730 --> 00:00:54,990 That was the default. 15 00:00:55,140 --> 00:01:00,600 But now I'm going to use Flash fill and the magic happens. 16 00:01:00,600 --> 00:01:05,250 It tries to recognize a pattern and it tries to apply that pattern. 17 00:01:05,250 --> 00:01:07,590 Let's try this with the last name. 18 00:01:07,590 --> 00:01:09,000 Let's type in "Miller". 19 00:01:09,000 --> 00:01:10,530 Now this time I'm gonna do it a bit different. 20 00:01:10,530 --> 00:01:15,320 I'm just going to continue typing so let's do the next one and look what happens. 21 00:01:15,330 --> 00:01:20,610 It tries to recognize a pattern and it already shows it to me in this light gray color. 22 00:01:20,610 --> 00:01:23,820 So all I have to do now is to press enter. 23 00:01:23,850 --> 00:01:30,770 That's the beauty of using Flash Fill. The only things you have to take into account is that one 24 00:01:30,810 --> 00:01:34,010 this is not dynamic because there is no formula behind this. 25 00:01:34,010 --> 00:01:39,930 So if I changed the last name of Richard here to something else it's not going to get reflected here. 26 00:01:40,380 --> 00:01:46,420 I need to either type it in manually or I have to apply Flash Fill again to the entire range. 27 00:01:46,470 --> 00:01:49,630 Also if I wanted to get the first name somewhere else. 28 00:01:49,630 --> 00:01:56,640 So if I wanted to get Gary here and all the first names here but my source data is here. Flash Fill is 29 00:01:56,640 --> 00:01:58,750 not going to recognize the pattern. 30 00:01:58,800 --> 00:02:03,890 So if I go and select Flash Fill, I get this message. 31 00:02:03,930 --> 00:02:11,100 It tried to look for some type of pattern and it didn't find it because it doesn't know where to look. 32 00:02:11,280 --> 00:02:15,860 OK so you have to use Flash Fill close to your original data set. 33 00:02:15,910 --> 00:02:17,030 Let's do another example. 34 00:02:17,040 --> 00:02:20,370 We want to get the email addresses for these people. 35 00:02:20,370 --> 00:02:22,140 They all work at the same company. 36 00:02:22,140 --> 00:02:24,390 So let's just start typing it in. 37 00:02:24,390 --> 00:02:26,000 I'm going to start with 38 00:02:26,000 --> 00:02:30,050 gary.miller@xelplus.com 39 00:02:30,060 --> 00:02:35,230 Now, I can start typing the next ones in but there is also a shortcut for Flash Fill. 40 00:02:35,340 --> 00:02:41,910 So if we highlight the area here by holding down the shift key and the down arrow key, now I'm going 41 00:02:41,910 --> 00:02:46,890 to press control+E. I have the email addresses of everyone in my list. 42 00:02:47,550 --> 00:02:49,470 Let's take a look at another case. 43 00:02:49,470 --> 00:02:52,770 I want to get the name of the person in proper case. 44 00:02:52,800 --> 00:02:59,810 So I have mixed cases here so some of them are proper case but this one is a small g and a small m. 45 00:02:59,820 --> 00:03:05,250 I want all the names with a capital letter for the first name, capital letter for the second name. 46 00:03:05,640 --> 00:03:08,770 So again I can start just typing it in. 47 00:03:08,780 --> 00:03:12,150 Now let me just show you another way of applying Flash Fill. 48 00:03:12,240 --> 00:03:14,610 The option is in the home tab. 49 00:03:14,680 --> 00:03:22,620 So before we apply it let's just highlight the area here and go to fill and select Flash Fill. 50 00:03:22,620 --> 00:03:22,830 Okay. 51 00:03:22,830 --> 00:03:29,640 So these are all the different ways you can apply Flash Fill, just pick the one that you are likely going 52 00:03:29,640 --> 00:03:30,500 to remember. 53 00:03:30,510 --> 00:03:31,650 Now let's do another one. 54 00:03:31,650 --> 00:03:38,190 We want to get the initials of the person so I have GM here, just continue typing this in. 55 00:03:38,220 --> 00:03:44,610 It recognizes a pattern and notice that for this one Robert Junior Spear, it gave me three letters 56 00:03:44,640 --> 00:03:45,780 which is perfect. 57 00:03:45,870 --> 00:03:52,200 I just have to press enter. Next one, I have the opposite case here. I have first name separate, last name separate. 58 00:03:52,200 --> 00:03:58,800 I want to get the complete name, start typing it in. This time I'm going to highlight the area 59 00:03:58,950 --> 00:04:04,890 and use my shortcut key Control+e, that gives me the full name. Here's a different case. 60 00:04:04,890 --> 00:04:10,740 We have the product label and we want to split whether this is for women or men. 61 00:04:10,740 --> 00:04:12,770 So we want to get the initial here. 62 00:04:12,810 --> 00:04:22,830 Now if I put W here and apply my shortcut Control+e. It's recognized the wrong pattern because 63 00:04:22,830 --> 00:04:28,800 it thought the W is for the watch but I want the W to be for the last initial. 64 00:04:28,800 --> 00:04:35,490 So in these cases where Flash Fill doesn't easily recognize a pattern you need to give it more data 65 00:04:35,520 --> 00:04:36,440 to work off. 66 00:04:36,480 --> 00:04:38,830 So I have to give it the second case here. 67 00:04:38,920 --> 00:04:43,070 M and now let's highlight this and press Control+e. 68 00:04:43,070 --> 00:04:45,610 Now it's recognized the pattern. 69 00:04:45,990 --> 00:04:48,150 Let's use Flash Fill on dates. 70 00:04:48,150 --> 00:04:51,960 We want to get the year only. Type in 2019. 71 00:04:51,960 --> 00:04:54,940 Now let's drag this down. 72 00:04:55,140 --> 00:05:02,460 Apply Flash Fill. And it's correctly recognized that we want to get the year from the dates right here. 73 00:05:02,540 --> 00:05:10,710 So these are different ways you can use this magic tool Flash Fill which is available since Excel 2013.