Flash Fill is the closest thing Excel has to mind reading. You show it one example of what you want, press Ctrl + E, and it does the rest of the column for you. No formulas, no functions to remember.
It’s available in Excel 2013 and later, including Microsoft 365 and Excel for Mac.
How Flash Fill works
Say column A has full names like Sarah Jones, and you want just the first names in column B.
- Click B2 and type Sarah.
- Press Enter to move to B3.
- Press Ctrl + E.
Excel looks at what you typed, works out that you took everything before the space, and fills in every first name down the column.
You can also find it on the Data tab. Click Flash Fill in the Data Tools group.
Things you can do with Flash Fill
Split data. Pull out first names, surnames, email usernames, house numbers or postcodes.
Join data. With first names in A and surnames in B, type Sarah Jones in C2 and press Ctrl + E. It combines them for every row.
Reformat data. Turn 07700900123 into 07700 900123, or sarah jones into Sarah Jones.
Build new values. Type S. Jones to get initials and surnames, or sa*********@*****ny.com to create email addresses from names.
Give it a better example
Flash Fill guesses from your examples. If the first result isn’t right:
- Type a second example in the next row, correcting the pattern, then press Ctrl + E again.
- Choose an example that shows the tricky case. If some people have middle names, use one of them as your example.
Always scroll through the results before you rely on them. Flash Fill is clever, but it isn’t perfect with messy data.
Turn on automatic Flash Fill
Often Excel will offer to Flash Fill as you type. The suggestions appear in grey and you just press Enter to accept them. If that never happens:
- Go to File > Options > Advanced.
- Under Editing options, tick Automatically Flash Fill.
- Click OK.
Flash Fill vs formulas
Flash Fill types values into the cells. It doesn’t create formulas. That means:
- If you change the original data, the Flash Fill results won’t update. Run it again.
- The results are easy to copy anywhere, because there’s nothing to break.
If the source data changes regularly, use a formula instead. TEXTBEFORE, TEXTAFTER and TEXTSPLIT in Microsoft 365 handle most splitting jobs.
Troubleshooting
Ctrl + E does nothing. Flash Fill needs to be directly next to the data, with a heading row and your example in the first row below it. Blank columns in between can stop it working.
“Flash Fill didn’t recognise a pattern.” Add another example or two. Very inconsistent data may need tidying first, see our guide on removing extra spaces.
It filled numbers as text. That’s normal for Flash Fill. Use Convert to Number from the green triangle warning if you need to calculate with them.
Wrapping up
Next time you’re about to retype a column, stop. Type one example, press Ctrl + E, and let Excel do the typing.
