You've got a column of full names. Your boss wants them split into first and last. Doing it by hand for 800 rows? Forget it. Flash Fill handles that in two keystrokes — Ctrl+E — and the same trick works for emails, phone numbers, dates, case changes, and a dozen other reformatting jobs that used to take a stack of LEFT, RIGHT, MID, and FIND formulas.
Flash Fill arrived in Excel 2013. Microsoft built it on a pattern-recognition engine that watches what you type in a column next to your data, guesses what you're doing, and fills the rest. No formula. No macro. No copy-paste. It's the closest Excel gets to actually reading your mind, and most spreadsheet users still don't use it.
This guide walks through every way to trigger Flash Fill — the keyboard shortcut, the ribbon button, the auto-suggest behaviour — plus the use cases where it shines, the situations where it quietly fails, and what to do when the preview just won't appear. We'll also cover the difference between Flash Fill and AutoFill, since the two get mixed up constantly and they're solving different problems.
One thing to know upfront: Flash Fill is non-formulaic. The result is plain text, not a live formula. If your source data changes later, Flash Fill won't update. That's a feature, not a bug — but you need to know it.
Flash Fill is a transformation tool. You give it a sample of the output you want, in a cell next to your source column, and it builds a pattern from your example. Type "John" next to a cell containing "John Smith" and Excel sees you're pulling the first name. Type one more — "Mary" next to "Mary Jones" — and you'll usually see a faint grey preview running down the column with every other first name suggested. Hit Enter to accept, or Ctrl+E to force the fill manually.
That pattern engine handles a surprising range of jobs:
5551234567 into (555) 123-4567PROPER()It's not magic. Flash Fill needs a consistent pattern. If half your names use middle initials and half don't, the engine guesses wrong on the inconsistent rows. We'll cover that gotcha further down — but for clean data, it's stunningly fast.
Type one or two examples in the column next to your source data, then press Ctrl+E. That's the entire workflow. If Excel doesn't see a pattern after two examples, give it a third. Anything beyond four examples usually means Flash Fill isn't the right tool for the job — switch to a formula or Power Query.
Treat Flash Fill as a fast cleanup pass, not a permanent transformation layer. The output is plain text, so if your source data is going to change, you'll re-run Ctrl+E each time. For columns that need to stay live, use a formula like TEXTSPLIT, LEFT, or SUBSTITUTE instead.
Excel gives you three entry points. Pick the one that fits your hand.
Type your example in the cell next to your first data row. Move to the cell below it. Press Ctrl+E. Excel scans the column, finds the pattern, fills every empty cell down to the last row of your source data. This is the fastest method and the one I use 95% of the time. On Mac, the shortcut is the same — Ctrl+E, not Cmd+E.
Go to Data on the ribbon. Look in the "Data Tools" group, between "Text to Columns" and "Remove Duplicates". Click Flash Fill. Same result as Ctrl+E. Use this when you forget the shortcut, or when you're showing a colleague who's never seen the feature.
By default, Excel watches you type. After you've entered one or two examples, you'll see a grey ghost preview of the rest of the column. Press Enter to accept. If the preview doesn't show up, your "Automatically Flash Fill" setting may be off — check File > Options > Advanced > Editing options and make sure the box is ticked.
One detail people miss: Flash Fill won't trigger if your output column is more than one cell away from the source column. Keep them adjacent. If you've got a buffer column in between, the pattern engine can't see what you're transforming.
Fastest method. Type your example in the cell next to the first source row, move down one cell, then press Ctrl+E. Excel scans down to the last contiguous row of source data and fills every blank output cell. Works identically on Windows and Mac, every Excel version from 2013 forward. This is the keyboard route every analyst commits to muscle memory.
Ribbon route for users who prefer mouse navigation. Click the Data tab, find the Data Tools group between Text to Columns and Remove Duplicates, then click Flash Fill. Same result as Ctrl+E. Useful when demonstrating the feature to a colleague, or when you've genuinely forgotten the shortcut. The icon shows a small lightning bolt over a column of cells.
Hands-free option. Excel watches what you type in an adjacent column and, once it spots a pattern, shows grey ghost text running down the rest of the column. Press Enter to accept the preview. If the ghost text never appears, open File > Options > Advanced and confirm the 'Automatically Flash Fill' checkbox is ticked under Editing options.
Lesser-known route. Right-click a cell, choose Fill, then Flash Fill from the submenu. Buried but useful when teaching the feature to someone who finds keyboard shortcuts intimidating. The behaviour matches Ctrl+E exactly — Excel learns from the cell above and fills down from there.
Here's where Flash Fill earns its keep — practical scenarios you'll run into every week.
Splitting first and last names. Column A has "John Smith", "Mary Jones", "Carlos Rivera". In B1 type "John", in B2 type "Mary", then Ctrl+E in B3. Done. Repeat for column C with last names. Two minutes for 5,000 rows.
Extracting the email domain. A1 has "jane@acme.com". B1: type "acme.com". B2: start typing the next domain and Excel previews the rest. Ctrl+E. Whole list filled.
Reformatting phone numbers. Raw data: "5551234567". Desired: "(555) 123-4567". Type the formatted example once, Ctrl+E, and Flash Fill keeps the parentheses, the space, and the dash in the right positions for every row — even when the source has dashes already, or country codes mixed in, as long as the digit count is consistent.
Initials from full names. "John Smith" becomes "J.S." — type the first one, Ctrl+E, watch the column populate.
Date reformats. "20260415" turns into "Apr 15, 2026". The engine handles the month-name lookup automatically if your example uses a recognised month abbreviation.
Concatenating with separators. First name in A, last name in B. You want "Smith, John" in C. Type "Smith, John" in C1, "Jones, Mary" in C2, Ctrl+E. Done — no & operator, no CONCAT, no TEXTJOIN.
Split full names into first, last, or middle components in seconds. Pull initials for badges or directories. Format as 'Last, First' for alphabetised lists. Convert 'JOHN SMITH' to 'John Smith' without resorting to PROPER(). Add titles like Mr or Ms when a separate gender column exists.
Give Flash Fill two examples — one regular name and one with a middle initial — and it handles both patterns at once. For a column with three thousand mixed-format names, this saves roughly an hour over manual cleanup or formula construction. The engine handles hyphenated surnames, apostrophes like O'Neil, and accented characters without extra coaxing.
Reformat raw digit strings into standardised formats. '5551234567' becomes '(555) 123-4567'. Strip country codes from international entries. Add dashes, spaces, or parentheses to match a corporate style guide. The pattern engine reads digit position rather than the original separators, so it works even when source rows are formatted inconsistently — as long as the digit count is the same.
Useful for CRM imports where vendor exports use one format and your downstream system expects another. Two well-chosen examples usually beat a half-page of nested SUBSTITUTE formulas, and the result is human-readable rather than buried in a 200-character formula cell.
Extract the username before the @ sign for a quick contact list. Pull just the domain to group customers by company. Split out the TLD when sorting by country. Build emails from a first + last name column using a 'firstname.lastname@company.com' rule.
One example is usually enough for the simpler jobs; two locks down the pattern for trickier ones with hyphens or numbers. Pairs naturally with VLOOKUP afterwards when you want to match the extracted domain back to a customer table.
Convert '20260415' to 'Apr 15, 2026' or '15/04/2026'. Switch between day-month-year and month-day-year for reports going to different regions. Pull the year alone when grouping records. Reformat ISO 8601 timestamps into human-readable summary lines.
One important note: Flash Fill treats dates as text patterns, not date values. The cell looks right but it won't sort chronologically or feed into date maths. For live date arithmetic use DATE, TEXT, or DATEVALUE formulas instead — keep Flash Fill for the one-off display reformat.
Join columns with custom separators. 'John' + 'Smith' becomes 'Smith, John' or 'J. Smith' or 'jsmith'. Add a fixed prefix like 'Mr.' or a domain like '@acme.com'. Build product SKUs from category + size + colour columns. Mix free text and column data — 'Order #' followed by the order ID followed by ' confirmed' all in one cell.
Faster than TEXTJOIN or the & operator for one-off cleanups. Result is static text, so re-run Ctrl+E if your source data changes — or use a formula if the dataset is going to keep growing.
This is the question that trips up almost everyone learning these features. AutoFill and Flash Fill look similar but they're solving completely different problems.
AutoFill extends a sequence. Drag the fill handle (that little square at the bottom-right of a selected cell) down a column and Excel continues whatever pattern it sees: 1, 2, 3, 4… or Mon, Tue, Wed… or Jan, Feb, Mar… It works with numbers, dates, weekdays, months, custom lists, and formulas. AutoFill is about generating data based on a starting value.
Flash Fill transforms an existing column. It needs a source column already populated, and it produces a new column by applying a rule it inferred from your example. Flash Fill is about reshaping data you already have.
Quick rule of thumb: if you're generating numbers, dates, or a series — that's AutoFill. If you're splitting, joining, reformatting, or extracting from existing data — that's Flash Fill. AutoFill lives on the fill handle. Flash Fill lives on Ctrl+E.
Flash Fill isn't infallible. Here are the situations where it stumbles, with the fixes.
Inconsistent source data. If half your names have middle initials and half don't, the engine often pulls "John Q" as the first name instead of "John". The fix: feed it more examples. Give Flash Fill two or three consistent samples — including one with a middle initial and one without — and it usually figures out the variation. Three examples beats two; four beats three.
The preview never appears. Check that "Automatically Flash Fill" is enabled in Options. Also confirm your output column is directly adjacent to the source. Buffer columns kill the suggestion engine.
Ctrl+E says "We couldn't see a pattern". You haven't given Excel enough to work with. Type a second example, then try again. Sometimes a third example is needed when the rule is subtle — like dropping the middle word from a three-word phrase.
Wrong column got filled. Press Ctrl+Z immediately to undo. Then check that your example matches the row beside it. A typo in your sample becomes the rule the engine learns from.
It worked, then I added new rows and nothing happened. Flash Fill is a one-shot operation. It's not a live formula. New rows in the source column won't auto-fill the new rows in the output column — you'll need to re-run Ctrl+E, or set up a real formula if the data is going to keep growing.
Flash Fill is built into every version of Excel from 2013 onwards on Windows. That includes Excel 2013, 2016, 2019, 2021, Microsoft 365, and Excel for the web. Excel 2010 and earlier — no Flash Fill, full stop. You'd need a custom VBA solution or one of the text functions.
On Mac, Flash Fill landed in Excel 2016 for Mac and works in every Mac version since. The shortcut is the same as on Windows — Ctrl+E. (Yes, Ctrl, not Cmd. This is one of the few Excel shortcuts where Microsoft kept the Windows binding on Mac.) The ribbon button lives in the same place: Data tab, Data Tools group.
To check whether automatic Flash Fill is enabled on your installation, open File > Options > Advanced (on Mac: Excel > Preferences > AutoCorrect) and scroll to the "Editing options" section. You'll find a checkbox labelled "Automatically Flash Fill". Toggle it on. If you prefer to control when Flash Fill runs — and a lot of power users do — leave it off and use Ctrl+E manually.
Excel for the web supports Ctrl+E and the Data tab button, but the auto-suggest preview behaves differently. The grey ghost text doesn't always appear. If you're in the browser version, default to the manual trigger.
Flash Fill is text manipulation. It doesn't do maths. It can't pull a SUM, it can't average a column, it can't apply a percentage. For any calculation, you still need formulas.
It also doesn't update. Once you've Flash Filled a column, the result is static. If your source column changes, your output column stays the same until you run Ctrl+E again. This is why a lot of analysts use Flash Fill for one-off cleanups and switch to formulas — TEXTSPLIT, LEFT, RIGHT, SUBSTITUTE — for any dataset that's going to keep growing.
Flash Fill can't reach across sheets. The source data and the output column must live in the same worksheet. If your source is on Sheet1 and your output is on Sheet2, the pattern engine won't see it.
And there's a row limit nobody talks about. Flash Fill fills up to the last row of contiguous data in the source column. Empty rows act as a stopping point. If your column has a blank row at row 50, Ctrl+E fills 1 to 49 and ignores everything below. Delete the blank rows, or fill the gap with a placeholder, before running.
If Flash Fill isn't behaving, run through this list before doing anything more complicated:
Run this sequence and 95% of Flash Fill problems clear up in under a minute.
Once you've used Flash Fill for simple splits, try it on these trickier jobs:
Mixed-case formatting. Source: "john SMITH". Output: "John Smith". Flash Fill handles per-letter case logic when you give it two consistent examples.
Partial extraction with rules. Source: "Invoice-2026-04567-USD". You want just the invoice number. Type "04567", give one more example, Ctrl+E. The engine picks up the position rule even when delimiters change.
Conditional output. Source column has product codes. Some start with "P", some with "S". You want "P-XXXX" as "Product XXXX" and "S-XXXX" as "Service XXXX". Give two examples of each branch and Flash Fill builds a small decision tree.
Punctuation cleanup. Source: "smith , john". Output: "Smith, John". Trimming, capitalisation, and the misplaced space all get learned from one corrected example.
Where Flash Fill stops being useful: complex multi-step transformations, anything involving lookups against other tables, or anything that needs to recalculate when the source updates. Those jobs need formulas or Power Query.
Flash Fill shows up on every modern Excel certification — MOS Excel Associate, MOS Excel Expert, and the general Microsoft Office Specialist tests for the 365/2019 versions. The exam objectives explicitly call out "use Flash Fill" as a required skill. Examiners typically present you with a column of jumbled data and ask you to produce a reformatted version using Flash Fill rather than formulas. Ctrl+E is the answer they're testing for.
It's also one of the most common questions in Excel job interviews for analyst roles. Hiring managers like it because the answer is short, the demonstration is fast, and it tells them whether you've worked with real-world messy data or just textbook examples. Knowing both methods — Ctrl+E and the Data tab button — is enough to score full marks.
If you're prepping for an Excel test, the question almost always pairs Flash Fill with Text to Columns. The two features overlap but they're not interchangeable. Text to Columns splits on a delimiter; Flash Fill learns from an example. Knowing when each one's the right tool is the deeper skill the test is probing.
Reading about Flash Fill won't make you fast at it. Open a workbook, drop in 50 rows of messy data, and run through five different transformations end-to-end. Split names, fix phone formats, extract domains, change case, reshuffle dates. Time yourself. Most people hit five-second-per-column speeds within a day of dedicated practice — and that compounds fast across a career of spreadsheets.
Below are practice question sets that test Flash Fill alongside other Excel features. Use them to lock the shortcut and the use cases into muscle memory. They're free, they run in your browser, and they cover the exact kinds of pattern-matching problems Microsoft puts on the MOS exam.
The shortcut is Ctrl+E on both Windows and Mac. Type an example in the cell next to your source data, move to the cell below, then press Ctrl+E. Excel scans your example, infers the pattern, and fills the rest of the column. The same shortcut works in Excel for the web, although the auto-suggest preview behaves differently in the browser version.
The most common reasons are: your version is older than Excel 2013, your output column isn't directly adjacent to the source, you haven't given enough examples (try two or three), automatic Flash Fill is disabled in Options, or your source data has blank rows breaking the range. Run through each of those before assuming the feature is broken — Flash Fill is reliable once the setup is right.
AutoFill extends a sequence — drag the fill handle and Excel continues numbers, dates, or formulas. Flash Fill transforms an existing column by inferring a rule from your example. AutoFill generates data; Flash Fill reshapes data. AutoFill lives on the fill handle. Flash Fill lives on Ctrl+E. They look similar but solve different problems.
Yes — Flash Fill has been in Excel for Mac since the 2016 release. The shortcut is the same as on Windows: Ctrl+E, not Cmd+E. The Data tab button lives in the same place too, under Data Tools. Auto-suggest works in newer Mac versions but feels slightly slower than on Windows.
No. Flash Fill produces static text, not a live formula. If your source column changes after you've run Ctrl+E, the output column stays the same until you re-run Flash Fill. For datasets that keep growing, use a formula like TEXTSPLIT, LEFT, RIGHT, or SUBSTITUTE instead — those recalculate every time the source updates.
Usually two. One example tells Excel what kind of transformation you want, and the second confirms the rule. For trickier patterns — middle initials, mixed punctuation, conditional output — give it three or four examples. If you've passed four examples and Flash Fill still gets it wrong, the data probably isn't consistent enough for the feature, and you'll need a formula or Power Query instead.
Flash Fill was introduced in Excel 2013 for Windows. It's been included in every desktop version since — 2016, 2019, 2021, and Microsoft 365 — as well as Excel for the web. Excel 2010 and earlier don't have Flash Fill at all. If you're stuck on an older version, the closest substitute is a combination of LEFT, RIGHT, MID, FIND, and SUBSTITUTE formulas.
Yes — and it's often faster. Text to Columns splits on a fixed delimiter (comma, tab, space). Flash Fill learns from your example, so it can split on positional rules, mixed delimiters, or patterns that Text to Columns can't reach. For clean delimited data, both tools work. For messy data with inconsistent separators, Flash Fill is the better choice nine times out of ten.