- Splitting full names into first names
- Joining columns together
- Jobs Flash Fill handles well
- Why Flash Fill isn't showing a preview
- The catch: Flash Fill doesn't update
- Flash Fill on a Mac and in the browser
- Putting cleaned columns to work
- Pulling details out of order notes
- What I'd do
- Frequently asked questions
To use Flash Fill in Excel, type the result you want for the first row in the column next to your data, press Enter, then start typing the second row. Excel spots the pattern and shows a grey preview for the rest of the column; press Enter to accept it, or press Ctrl+E to run Flash Fill straight away.
Flash Fill is the closest thing Excel has to mind reading. You show it one example of what you want, say the first name pulled out of a full name, and it fills the whole column the same way. No formulas, no functions to remember. For splitting a messy export of client details into tidy columns, it's the quickest tool Excel has.
Splitting full names into first names
Say column A holds full names like Sarah Jones, and you want first names in column B.
- Click B2 and type Sarah, the first name from A2. Press Enter.
- In B3, start typing the first name from A3. Excel shows a grey preview of first names all the way down.
- Press Enter to accept the preview.
- If no preview appears, click B2 and press Ctrl+E, or go to Data and select Flash Fill.
Microsoft describes exactly this: Flash Fill fills your data when it senses a pattern, and can separate first and last names from one column or combine them from two.
Joining columns together
It works the other way too. With first names in A and surnames in B, type Sarah Jones in C2, press Enter and start typing the next full name in C3. Microsoft's own walkthrough uses this example. You'll get the full name for every row without writing a CONCAT formula.
Jobs Flash Fill handles well
| Messy data | Type this as your example | Flash Fill gives you |
|---|---|---|
| sarah jones | Sarah Jones | Every name with capital letters |
| Sarah Jones | S. Jones | Initial and surname |
| [email protected] | shop.co.uk | The email domain |
| Order 7, Leeds | Town: Leeds | A town label on every order line |
The more consistent your data, the better it guesses. If some rows follow a different pattern, give it a second example in the next row and it usually corrects itself.
Why Flash Fill isn't showing a preview
Microsoft says the most likely reason is that Flash Fill isn't switched on. You can still run it by hand with Data, Flash Fill or Ctrl+E. To turn the automatic preview back on, open Excel's options, go to Advanced, and under Editing Options tick Automatically Flash Fill.
Other common causes:
- Your example column isn't right next to the data. Flash Fill looks at the columns beside it, so leave no gap.
- There's a blank row in the middle of the data, which can stop the fill partway.
- The pattern is ambiguous. Give two or three examples before pressing Ctrl+E.
The catch: Flash Fill doesn't update
This is what people miss. Flash Fill types values, not formulas. If you change Sarah to Sally in column A, the first name in column B stays as Sarah. That's fine for a one-off clean-up of an export, but for a list you keep editing, a formula such as =CONCAT(A2," ",B2) or a TEXT function keeps the result in step with the source.
It also guesses. On a long list, scroll through the results before you trust them, especially names with middle names, double-barrelled surnames or business names. One odd row can throw the pattern for everything below it. I always check the first ten rows, the last ten, and anything that looks unusually long.
Flash Fill on a Mac and in the browser
Microsoft lists Flash Fill for Excel on Windows and Mac, including Excel 2016 onwards and Excel for Microsoft 365. On a Mac the Data menu route works the same way.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Putting cleaned columns to work
Once your names, towns or codes are in tidy columns, you can sort, filter and count them cleanly. If you want averages from those columns, such as orders per client, my guide to dividing in Excel picks up from there. For more ways to join columns, my guide to combining two columns in Excel compares Flash Fill with formulas, so you can pick the right one for each list.
Pulling details out of order notes
Exports from online shops and booking forms often cram several details into one cell. A line such as Order 7, Leeds, Gift wrap is easy to read but awkward to sort. Add three columns beside it and give Flash Fill one example in each: the order number in the first, the town in the second, the extra in the third. Press Ctrl+E in each column and you've turned one messy field into three you can filter.
It doesn't always get it right first time, and that's fine. When a row comes out wrong, correct that one cell by hand and Excel adjusts the rest of the column to match the new example. Two or three corrections on an awkward list usually get it there, which is still far quicker than retyping everything.
What I'd do
Use Flash Fill for one-off clean-ups: type one example, press Ctrl+E, then check the results. Keep the original column until you're sure. For lists that change every week, use a formula instead so the results stay up to date. It's one of my favourite Excel features because it saves so much fiddly retyping.
Frequently asked questions
What is the shortcut for Flash Fill in Excel?
Ctrl+E. You can also go to the Data tab and select Flash Fill.
Why is Flash Fill not working?
It may be switched off. Turn on Automatically Flash Fill in Excel's Advanced options, or run it manually with Ctrl+E.
Does Flash Fill update when the data changes?
No. It fills in values, so you'll need to run it again or use a formula if the source changes.
Can Flash Fill split first and last names?
Yes. Type the first name for the first row, then press Ctrl+E, and repeat in another column for surnames.
