Flash fill is a great Excel tool. Learn how to use flash fill to automatically extract data, combine data, and much more.

What is Flash Fill?

• It’s a time-saving feature in Excel that automatically fills cells with data based on patterns it recognizes.
• It’s available in Excel 2013 and later versions.
• It can handle various tasks like splitting names, combining text, formatting data, and more.

Example 1

Use flash fill in Excel to extract the numbers in column A below.

1. First, tell Excel what you want to do by entering the value 4645 into cell B1.

2. On the Data tab, in the Data Tools group, click Flash Fill (or press CTRL + E).

Result:

Example 2

Use flash fill in Excel to combine the last names in column A below and the first names in column B below to create email addresses.

1. First, tell Excel what you want to do by entering a correct email address in cell C1.

2. On the Data tab, in the Data Tools group, click Flash Fill (or press CTRL + E).

Result:

Example 3

Use flash fill in Excel to reformat the numbers in column A below.

1. First, tell Excel what you want to do by entering a correct social security number in cell B1.

2. On the Data tab, in the Data Tools group, click Flash Fill (or press CTRL + E).

Result:

Example 4

Use flash fill in Excel to rearrange the numbers in column A below.

1. First, tell Excel what you want to do by entering a new credit card number in cell B1 (for example, swap the first 4 numbers with the last 4 numbers).

2. On the Data tab, in the Data Tools group, click Flash Fill (or press CTRL + E).

Result:

Example 5

Use flash fill in Excel to extract letters from text strings.

1. First, tell Excel what you want to do by entering the letters from the first text string in cell B1.

2. On the Data tab, in the Data Tools group, click Flash Fill (or press CTRL + E).

Result:

Flash Fill Limitations

Flash fill in Excel only works when it recognizes a pattern, and sometimes needs a little help. For example, use flash fill in Excel to extract the numbers in column A below.

1. First, tell Excel what you want to do by entering the value 130 into cell B1.

2. On the Data tab, in the Data Tools group, click Flash Fill (or press CTRL + E).

Result:

Note: flash fill did not correctly extract the decimal numbers (only the digits after the decimal point).

3. Immediately after executing step 2, change the value in cell B3 to 26.2 and Excel will correctly extract all other decimal numbers for you.

Finally, flash fill does not automatically update your results when your source data changes.

4. For example, if you change the number in cell A1 to 200, Excel will not update the number in cell B1.