How To Use Flash Fill in Excel

Jan 31, 2020 • edited Feb 01, 2020

How To Use Flash Fill in Excel

Let's say column A contains first names, column B has last names, and you want to fill column C with first and last names combined. If you establish a pattern by typing the full name in column C, Excel's Flash Fill feature will fill in the rest for you based on the pattern you provide.

Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns.

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.

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

Result:

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.

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

Result:

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.

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

Result:

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.

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. 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.

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.

#How To#Tutorial#AutoFill#Range

How to Subtract Cells in Excel

How To Use The Union And Intersect Operator In Excel