Flash Fill in Excel

 

Flash Fill Example 1 | Flash Fill Example 2 | Flash Fill Example 3 | Flash Fill Limitations

Use flash fill in Excel 2013 or later to automatically extract or to automatically combine data. Flash Fill in Excel only works when it recognizes a pattern.

Flash Fill Example 1

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 4645 into cell B1.

Flash Fill Example 1

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

Click Flash Fill

Result:

Flash Fill in Excel

Flash Fill Example 2

For example, use flash fill in Excel to join 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.

Flash Fill Example 2

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

Click Flash Fill

Result:

Flash Fill in Excel

Flash Fill Example 3

For example, 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.

Flash Fill Example 3

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

Click Flash Fill

Result:

Flash Fill in Excel

Flash Fill Limitations

Flash fill is a great Excel tool. However, it has a few limitations. 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.

Flash Fill Example 4

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

Click Flash Fill

Result:

Flash Fill in Excel

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

3. Flash fill needs a little help sometimes. 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.

Flash Fill Changed Cells

4. Flash fill in Excel does not automatically update your results when your source data changes. For example, if you change the number in cell A1 to 200, Excel will not update the number in cell B1.

Flash Fill Does Not Update When Source Data Changes