5 Uses of Excel's 'Superhero' Flash Fill

Quick reference

• 00:04 In this lesson, we're going to take a look at one of my personal favorite
• 00:09 things in Excel, and that is Excel's superhero, Flash Fill.
• 00:13 And if you've never used Flash Fill before, then you are really in for
• 00:17 a treat,
• 00:18 because it is really one of the best tools that we have when it comes to efficiency.
• 00:23 So I've got five examples here of different ways that you can use Flash Fill
• 00:28 to do numerous different things with your data.
• 00:31 And you will find that Flash Fill negates the need in some instances to use longer,
• 00:37 more complex formulas.
• 00:38 So let's dive in, and take a look at the first example.
• 00:42 So, notice here, I have a reasonably long list of addresses.
• 00:47 And, I've been tasked with breaking up these addresses across the columns B to F.
• 00:54 So, we need the street numbers in one column, the street name, the town city,
• 00:58 the state, and the zip code in their own separate columns.
• 01:01 And this is something you often find,
• 01:03 particularly if you're importing data from an external system, is that a lot of
• 01:07 the time you'll have things like addresses all squashed into one cell.
• 01:11 And if you do plan to go on and maybe put this data into a PivotTable or
• 01:15 create a PivotChart, it's always more beneficial to make sure that you have
• 01:19 every single element of that address split up into his own separate column.
• 01:23 Now there are numerous different techniques in Excel that we can use
• 01:26 to do this.
• 01:27 We could possibly use text columns or we could give some text functions a go.
• 01:32 But by far the easiest method is simply to use Flash Fill.
• 01:37 Now if you're not aware of Flash Fill, then you are really in for
• 01:41 a treat because it's one of the most useful utilities in Excel.
• 01:45 And the reason why it's useful is because it takes all of the hard work out of tasks
• 01:49 like this.
• 01:50 Now the way that Flash Fill works is that it looks for a pattern and then it
• 01:54 basically just replicates that pattern for every row of your table or your data set.
• 01:59 So all I need to do here is tell Excel what the pattern is, and
• 02:03 I do that by simply typing in the first one.
• 02:06 So I'm telling Excel exactly how I want to break up this address.
• 02:10 Now, check this out because this is where the magic happens.
• 02:13 All I now need to do to complete the rest of them is click in the first column and
• 02:18 press the keyboard shortcut Ctrl E, how magical is that?
• 02:22 Click in the next column, Ctrl E.
• 02:25 If I want to execute Flash Fill from the ribbon as opposed to using the shortcut,
• 02:29 I can simply jump up to the Data tab, and in the data tools group,
• 02:33 I can click the Flash Fill button.
• 02:35 So you can use whichever method you prefer.
• 02:39 But I think you'll agree that is so
• 02:41 much quicker than messing around with complicated formulas or text to columns.
• 02:47 Now in this scenario, I want to use Flash Fill to do the opposite of what we just
• 02:52 did, I want it to join text together.
• 02:54 So I have a first name in column A, and last names in column B.
• 02:59 And really what I want is, I just want the full name in column C.
• 03:03 Now, again, we could use text to columns or a formula, or we could use Flash Fill.
• 03:09 So all I need to do is tell Excel what the pattern is, so
• 03:13 I need to provide it with the first one.
• 03:15 I'm going to press Ctrl Enter to stay in the same cell,
• 03:18 now I can simply press Ctrl E, which is the keyboard shortcut for Flash Fill.
• 03:23 But just so you know where it is on the ribbon, if we jump up to the Data tab,
• 03:28 in the Data Tools group, this button here is your Flash Fill button.
• 03:32 So if I click this, it's going to do exactly the same as the Ctrl E shortcut,
• 03:37 and it's going to copy that pattern down.
• 03:39 Nice and simple.
• 03:41 Let's move on to example number three.
• 03:44 We can use Flash Fill to add additional text into existing data.
• 03:49 So maybe at our company all of our email addresses have a similar format.
• 03:53 Maybe it's the first name dot last name at, whatever the company name is.
• 03:58 So again, I just need to tell Excel the pattern.
• 04:01 So we're going to have ervin.webster@company.com,
• 04:06 Ctrl Enter to stay in the same cell, Ctrl E.
• 04:10 And check it out, I've saved so
• 04:12 much time by not having to type those all in individually.
• 04:16 Let's take a look at another example.
• 04:20 Now we can also use Flash Fill to do things like change the case.
• 04:24 So again, maybe I need to have all of these names in upper case.
• 04:29 So once again, all I need to do here is tell Excel the pattern.
• 04:34 So I want all of these names to be in upper case,
• 04:38 Ctrl Enter to stay in the same cell.
• 04:41 Now I'm going to show you another way that you can invoke Flash Fill aside from
• 04:45 the two methods I've already shown you.
• 04:47 If you go to the next cell just below and
• 04:49 just start to type in the next one, notice it goes down the results.
• 04:54 So if this is exactly what I want, I can press Enter.
• 04:57 If it's not, I can press esc to cancel the Flash Fill.
• 05:01 Well, this looks like what I want to do, so let's press enter, and
• 05:06 like magic I've converted all of those names to upper case.
• 05:09 Now in our final example, again,
• 05:12 we just want to extract the code from a phone number.
• 05:15 So maybe I just want the state code, I can simply tell Excel what the pattern is and
• 05:20 it's going to extract the correct details.
• 05:22 So I just want the state code, so that's what we have in brackets.
• 05:25 So for the first one it's 977, Ctrl Enter, Ctrl E, and
• 05:30 within a couple of seconds I've completed that task.
• 05:35 So those are some of the amazing ways that you can use Flash Fill to make yourself
• 05:40 more efficient when you're trying to do things like split up text, join text,
• 05:45 extract certain information, and also add information to existing text.
• 05:49 Now a lot of people say to me, that's brilliant, so
• 05:52 why do we need to have things like the left and
• 05:55 the right function to extract text when we now have Flash Fill.
• 05:59 Well, Flash Fill doesn't work in every single scenario and that is why
• 06:03 it's still useful to know things like text functions and text to columns.
• 06:07 So, let me show you an example of where Flash Fill doesn't work.
• 06:10 Now if I wanted to extract the first name and the last name and
• 06:15 just input them into column G using Flash Fill, I'm going to get a problem.
• 06:20 So if I type in, Ervin Webster,
• 06:25 Ctrl Enter, Ctrl E, I'm going to get this error message.
• 06:30 Now, the reason why I'm getting that is because I have blank
• 06:35 columns in between columns A and B and the output cells.
• 06:39 So if you do have blank columns and you're trying to use Flash Fill somewhere over
• 06:43 here in the spreadsheet, it's not going to work.
• 06:46 Your column needs to be right next to the data that you want to flash fill down.
• 06:50 And in a lot of scenarios you don't have the column that you want to use right next
• 06:55 to your data, so that is where we would then have to use something
• 06:59 like text functions or text to columns.
• 07:01 So just be aware of that, Flash Fill is brilliant, but
• 07:05 it doesn't work in every single situation.

Lesson notes are only available for subscribers.