How to use Flash Fill in Excel 2013

Flash Fill is one of the most important features newly added in Excel 2013. Flash Fill technique can recognize a pattern in a text and you can separate part of a text in a new column using this pattern recognition capability of Flash Fill. The beauty of Flash Fill is that separation happens within a flash and does not need any kind of formula.

Rather than manually separating first, middle, or last names in new columns, you can use Flash Fill to quickly and effectively do the job. And here’s how you do it:

Separating First, Middle, and Last Names

Say you have some names. For example, one name is ‘Robert De Niro’. Other names also consist of three parts like ‘Robert De Niro’. You want to separate the first name, middle name, and last name in separate columns. With fill-flash, you can do it easily.

Step 1:

Enter the first name in the column next to your data and Press Enter.

Excel 2013 new features

Step 1

Step 2:

Start typing the next name. Fill Flash will suggest the first part of all names. If it is what you want, press Enter.

Excel 2013 new features

Step 2

Step 3:

Now we type the last part of the name in the next column. We type it in lowercase and press Enter.

Excel 2013 new features

Step 3

Step 4:

Start typing the next name. Fill flash will suggest the last name this time in lowercase. Press Enter. You can separate the middle name the same way.

Excel 2013 new features

Step 4

Note1: Names you select must be of the same pattern. All the names will be 3 parts or 2 parts or 4 parts or ‘n’ parts. ‘n’ can be any natural number {1, 2, 3, 4, 5, 6, …}.

Note 2: Flash Fill feature can also be used to create new data from multiple columns. To do this, just provide a few examples of how you want the data combined, and Excel will figure out the pattern and fill in the column automatically. Using Flash Fill to create data seems to work much better than using it to separate data. But then again, it’s also easier to create formulas to create data from existing columns :).

Note 3: The main limitation of using Flash Fill is, Flash Fill is not a dynamic technique. If your data changes, columns created by Flash Fill do not update automatically (you must use a formula to update automatically then).

Read More: Flash Fill Not Recognizing Pattern in Excel (4 Causes with Fixes)

Splitting text in Excel using Flash Fill

The Text to Columns Wizard works very well for many types of data. But some data can’t be split by this wizard. For example, you can’t split data if it is not fixed-width or the data doesn’t have delimiters in this technique ( Text to Columns Wizard). In this type of case, the Flash Fill feature can help you. Keep in mind that Flash Fill works successfully only when the data is consistent.

Flash fill uses pattern recognition to separate data. For example, say you have a column that contains text in several rows.

The following image shows a worksheet with some text in a single column. Our goal is to extract the number part from each cell and put these numbers into a separate column. The Text to Columns Wizard can’t do this separation because the space delimiters aren’t consistent.

Delimiters are not consistent means: for example, in the first row, the number 20 is placed after 3 spaces, in the second row 6 is placed after 2 spaces, and so on. So we can’t separate numbers from this text using the “Text to Columns Wizard”. You can write an array formula to separate this type of number from the text, but writing an array formula is complicated.

Splitting text in Excel using Flash Fill

We shall separate numbers 20, 6, 9.5, 3.14159, 5, 3.12, 15, 7, 16, 90210 from the column.

  • Step 1: To use Flash Fill with our sample example, click on cell B1 to select it. Enter first number 20 in cell B1. Move to cell B2, and type the second number 6 in this cell. Now choose Data ➪ Data Tools ➪ Flash Fill (or press CTRL+E). You will see that the rest cells are automatically filled by Excel. Your new column will look like the following image.
Splitting text in Excel using Flash Fill

See Excel makes some incorrect guesses when we manually input the numbers in cells B1 and B2.

  • Step 2: As you see in the above screenshot, Excel has identified most of the values accurately. But some wrong guesses are also there. Accuracy will increase if you provide more examples. For example, as some of your numbers have decimal values, you can input a decimal number in the new column. Delete the suggested values, enter 3.12 in cell B6, and press CTRL + E. or You can put 3.14159 in the B4 cell. This time, you will find that Excel has got them all correctly. See the following screenshot.
Splitting text in Excel using Flash Fill

After you have entered an example of a decimal number, Excel gets them all correct.

Some important points you have to remember about Flash Fill:

  • You must check your data very carefully after using Flash Fill. If you get that the first few rows are correct, and you assume that Flash Fill worked correctly for all rows, then it may occur serious mistake.
  • Flash Fill increases accuracy when you provide more examples.

Read More: [Solved!] Flash Fill Not Working in Excel (5 Reasons with Solutions)

Download Working File

Download the working file from the link below:

Related Articles



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply