A senior on my team had a list of 2,400 client names that needed splitting into first name and last name in separate columns. I budgeted my afternoon. I started typing.
She walked past, typed the first name once, pressed two keys, and the entire column filled itself. The whole thing took her four seconds.
That trick is called Flash Fill. It has been in Excel since 2013. Most graduates have never heard of it.
Here's what it does, how to use it, and the five jobs where it'll save you an afternoon.
What Flash Fill is
Flash Fill watches what you type. The moment it spots a pattern, it offers to fill the rest of the column for you. You don't write a formula. You don't open a menu. You just type one example and press the keystroke.
That's it. Type one example next to your source data, press the keystroke, and Excel does the rest.
Mac note: use the actual Control key, not Command. ⌘ Cmd + E centres text in Excel for Mac and won't trigger Flash Fill. If for some reason Control + E doesn't work on your Mac (older Excel versions or remapped keyboards), the backup combo is ⌥ Option + ⌘ Cmd + E.
Five jobs Flash Fill does in seconds
Column A has Sarah Johnson, Tom Patel, Niko Tomoff, and 2,400 more. You need them split.
Type Sarah in B1. Press Ctrl + E. Every other cell in column B fills with the first name. Repeat in column C for the last name.
You've got a list of new joiners and need to build their first.last@firm.co.uk addresses for the welcome pack.
Type the full first example. Excel works out the pattern (lowercase, dot between names, fixed domain) and fills the rest correctly.
The CRM exported numbers as 07911 123 456 with spaces. The new system needs them as 07911123456 with no spaces.
Type one cleaned number. Press Ctrl + E. Done. Saves you opening Find & Replace four times.
You've got full UK postcodes and need only the outward part for regional analysis.
Flash Fill recognises "everything before the space" as the pattern and extracts it from every row. Works with mixed lengths.
An export gave you dates in three different formats. You need them all as DD-MMM-YYYY for the client report.
Mixed formats sometimes need two examples before Flash Fill catches on. Type the first, press Ctrl + E, check the results. If anything's wrong, correct one cell and press Ctrl + E again — Flash Fill learns from the correction.
When Flash Fill doesn't work
Sometimes Ctrl + E does nothing. Three common reasons:
- The data isn't directly adjacent. Flash Fill needs your example to sit in the column right next to the source. Empty columns between source and output break it.
- One example isn't enough. If the pattern is ambiguous (mixed formats, partial matches), give Flash Fill two or three examples before pressing the keystroke.
- The shortcut is mapped to something else. Go to Data tab → Flash Fill button. Same feature, slower path.
And one critical warning: Flash Fill produces static text, not a formula. If the source data changes later, the Flash Fill output does not update. For one-off cleaning that's fine. For a live report, use a formula instead — TEXTSPLIT, LEFT, RIGHT, or MID depending on the job.
One example. One keystroke. Two thousand rows done.
The bigger lesson
Half of looking competent in corporate Excel is knowing about features that have been sitting there for ten years. Flash Fill, Tables, Power Query, conditional formatting — all of them have been in Excel since most current managers were graduates. None of them get taught at university.
The seniors who look fast aren't smarter. They've just bumped into these features over the years and remembered them. Now you have Flash Fill in your toolkit too. Use it once today and it sticks for life.
The whole toolkit on one page.
Flash Fill is one of 15 shortcuts on the Grid & Formula survival sheet. Alongside 8 core formulas (XLOOKUP, SUMIFS, IFS, INDEX/MATCH), the data-cleaning helpers and the Google Sheets equivalents. Printable PDF, instant download.
Get the sheet — £11