6 ways to save time using Flash Fill in Microsoft Excel

Flash Fill showed up in Microsoft Excel 2013, so you’re probably already using it. However, you might not realize just how flexible and powerful this tool is. It’s a quick way to enter new or clean up existing data. In the past, you might have needed a function or expression to get what you need. Now, you enter the pattern you want and press Ctrl+E—no fuss, no muss. In this article, I’ll show you 10 ways Flash Fill can make your work just a bit easier by automatically anticipating your fill needs after making an entry or two.

SEE: 69 Excel tips every user should master (TechRepublic)

There are two requirements when using this feature:

• The source data must conform to a consistent pattern.
• You must refresh the Flash Fill results after updating the source data.

Perhaps the best feature of all is that the results are explicit data, and not the results of a function. That makes the results easier to work with. 

I’m using Microsoft 365 on a Windows 10 64-bit system, but Excel supports this feature all the way back to Excel 2013. You can work with your own data or download the demonstration .xlsx file. The browser edition supports Flash Fill.

1. How does Flash Fill work?

Using Flash Fill is simple, but you’ll need to let Excel know what you want. There are three steps:

1. Enter the source data.
2. To the right of the source data, enter what you want in the first cell.
3. Press Ctrl+E to implement Flash Fill, which will complete the list.

Occasionally, you’ll need to make two or three entries before Excel catches on.

This first example is a good lesson in how Flash Fill works. Because the data is inconsistent, the results are inconsistent, as you can see in Figure A. Flash Fill does its best; the problem is the inconsistent data, not Flash Fill. In this case, Flash Fill simply extracts characters from the right to the left, until it encounters the first space—that’s the only pattern it can discern.

Figure A

Now that you know how not to use Flash Fill, let’s continue with some examples of how Flash Fill can help you work more productively.

SEE: Windows 10 20H2 update: What you need to know (free PDF) (TechRepublic)

2. How to use Flash Fill to extract text

Let’s suppose you want to extract the first names from the source data in column B in the sheet shown in Figure B. To do so, enter Susan in C3 and press Ctrl+E. As you can see, the source data still has an inconsistency, but this time, the results are better than the first example. If you decide to change the source data so it’s consistent, don’t forget to update the Flash Fill—you’ll have to run it again.

Figure B

3. How to use Flash Fill to combine text

In the last example, we extracted the first name from a cell that contains full names. You can also combine names separated in different cells. Figure C shows the results of entering Harkins, Susan into cell D3, entering Doe, into D4, and then pressing Ctrl+E. By that time, Flash Fill was able to complete the pattern and fill in the rest. Not only did Flash Fill combine the names, but it also transposed them and added the comma!

Figure C

Notice that I cleaned up the data a bit to make it more consistent. It can take a few tries to catch on to that consistent-data requirement, so don’t worry if you run into these problems. I’m including them on purpose so you can see the problem and learn how to avoid it.

4. How to use Flash Fill to change case

You might have noticed that the name ALEXIS in cell B7 is still uppercase. Sometimes, you might not be able to cleanup the source data; you might be working with linked data or perhaps there are just too many records to fool with. This is a great opportunity to put Flash Fill to work, and you could even replace the source data with the results if you wanted.

Figure D shows the results of entering Alexis in cell E7 and then pressing Ctrl+E. Yes, Flash Fill can work up! You make the correction only once. Flash Fill will fix them all once you show it the pattern.

Figure D

5. How to use Flash Fill to extract numbers or text

Earlier, we used Flash Fill to extract first names from cells that contained full names. Data doesn’t have to be separated by a space and doesn’t have to be all text. Figure E shows several strings that contain both numerical digits and alpha characters. To extract only the numbers, enter the numbers from the first two strings by sight and then press Ctrl+E to fill in the remaining cells. Because the pattern is complex, Flash Fill needs a couple of examples to pick it up. As you can also see in Figure E, it works just as easily with alpha characters.

Figure E

6. How to use Flash Fill to extract and add

Don’t stop with just one task because Flash Fill can handle more than one change at a time. As you can see in Figure F, Flash Fill added a $ symbol and the letter K to the digits. I entered $80K into cell C3 and used Flash Fill to fill in the rest. Notice however, that the consistency issue created an error in C—it should be $1M to maintain the pattern. Flash Fill can’t do everything!

Figure F

These are just a few quick examples of how you can put Flash Fill to work for you. I’ve included a few monkey wrenches, so when it happens to you, you’ll know why and how to fix the source data if possible to meet the consistency requirement. 

Also see