How to use Flash Fill in Excel

Splitting names, pulling out email usernames or reformatting codes can eat an afternoon. With Flash Fill it takes a second. Show Excel one example and it finishes the whole column for you. It's the shortcut that makes colleagues ask how you did it so fast.

Step by step

1. Put your data in one column, for example full names in column A.
2. In the next column, type the result you want for the first row, for example just the first name.
3. Move to the cell below and press Ctrl+E.
4. Check the filled results. If one looks wrong, correct it and Flash Fill updates the others.

Tips & troubleshooting

• Flash Fill works best when the column sits right next to your data, with no blank columns in between.
• The results are fixed values, not formulas, so they won't update if the original data changes.
• If Ctrl+E does nothing, give Excel a second example so the pattern is clearer.

What Flash Fill is good at

• Splitting: first names, surnames or postcodes out of a longer cell.
• Combining: first name and surname into one cell, or building an email address from a name.
• Reformatting: 07700900123 into 07700 900123, or a product code into upper case with hyphens.
• Extracting: the domain from an email address, the year from a reference number, the initials from a name.

Other ways to run it

• Data tab: click Flash Fill in the Data Tools group.
• Home tab: click Fill, then Flash Fill.
• Automatically: as you type the second example, Excel may show the rest of the column in grey. Press Enter to accept it. If this never appears, check File, Options, Advanced, Automatically Flash Fill.

Flash Fill or a formula?

Flash Fill produces fixed values. That is perfect for a one-off clean-up of data you have just been sent. It is the wrong tool when the source will change, because nothing updates.

For data that changes, use a formula. In Microsoft 365, =TEXTBEFORE(A2," ") returns the first name, =TEXTAFTER(A2,"@") returns an email domain, and =TEXTSPLIT(A2," ") spreads every word into its own column.

Getting reliable results

• Check the tricky rows. Middle names, double-barrelled surnames and missing values are where the pattern breaks.
• Give two or three examples when the data is inconsistent, and include one of the awkward cases.
• Keep the example column directly beside the data, with a header in each column.
• Flash Fill only works down columns. It will not fill across a row.