Locked lesson.
About this lesson
Learn the different ways Flash Fill can be used to combine, split and change items in Excel lists.
Exercise files
Download this lesson’s related exercise files.
04-07-Uses of Excel's Superhero Flash Fill-Start.xlsx51.7 KB 04-07-Uses of Excel's Superhero Flash Fill-Complete.xlsx
60.2 KB 4.07 5-uses-of-excels-superhero-flash-fill - Exercise.docx
43.5 KB 4.07 5-uses-of-excels-superhero-flash-fill - Exercise solution.docx
96.8 KB Exercise - Uses of Flash Fill.xlsx
15.4 KB Exercise Solution - Uses of Flash Fill.xlsx
16.4 KB
Quick reference
5 Uses of Excel's 'Superhero' Flash Fill
Use Excel's 'Superhero' Flash Fill to quickly auto-complete and fill down data.
When to use
We use Flash Fill whenever we want to auto-fill data based on a pattern we have specified.
Instructions
The Flash Fill utility was released in Excel 2013 and is a game-changer. Flash Fill takes the hard work out of splitting data, combining data, extracting data from cells, and much more.
Let's look at 5 examples of Flash Fill.
Flash Fill - Split Data Across Columns
In this example, we need to split up the address in column A so the street number, street name, state, and zip code are in separate columns.
- Type in the first one so Excel knows the pattern.
- Click in each column and press CTRL+E.
Flash Fill - Join Text
In this example, we need to join the First Name and the Last Name in column C.
- Type the first one - Ervin Webster - into the cell.
- Press CTRL+E.
Flash Fill - Add Text
In this example, we want to complete the email address for each employee.
- Type an example of the first one.
We can use other methods to invoke Flash Fill.
- From the Data tab, in the Data Tools group, click Flash Fill.
Flash Fill - Change Case
We can use Flash Fill to change the case of text in a range of cells.
- Type an example of the first one.
- Press Enter.
- Start to type the next name in the list.
Notice how Excel 'ghosts' the rest of the names down.
- Press Enter to accept.
Flash Fill - Extract Data
In this example, we need to extract the state code from the phone number.
- Type an example of the first one.
- Press CTRL+E.
Hints & tips
- Flash Fill can't be used in all situations. For example, if there are blank columns in the data then Flash Fill will not work.
- 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.