How to use Flash Fill in Excel

Flash Fill is the fastest cleanup tool in Excel, and most people never touch it. You show Excel one example of what you want, start the next one, and it finishes the column — splitting, joining, or reformatting hundreds of rows in a second. No formula, no Text to Columns dialog.

The one move that matters

Put your messy data in a column. In the next column, type the result you want for the first row by hand. Start typing the second row — and when Excel shows a grey preview of the rest, press Ctrl + E (Mac: Ctrl + E too). That's it. Excel infers the pattern and fills everything below.

If no preview appears, type two examples instead of one, then press Ctrl + E. Two rows is almost always enough for Excel to lock onto the pattern.

What it's actually good for

Split "Last, First" into two columns. Type the first name once; Flash Fill separates all of them.
Pull an invoice or PO number out of a longer string. INV-10428 / North clinic → 10428.
Reformat phone numbers. 6025551234 → (602) 555-1234.
Join columns into one. First + Last → First Last, or build a filename like 2026-10_North_report.
Fix capitalization — ACME inc → Acme Inc — when the pattern is consistent.

Where it trips up

Flash Fill is one-time, not live. If the source data changes later, the filled column does not update — re-run it, or use a formula (LEFT, RIGHT, TEXTSPLIT) when you need something that recalculates. It also needs consistent source data; if row 40 is formatted differently from rows 1–39, Flash Fill will guess wrong there, so always scan the result.

When to use something else

For a true split on a clear delimiter (comma, tab, fixed width), Text to Columns is more predictable. For anything that must stay in sync with changing data, use a formula. Flash Fill is for the fast, one-off "just clean this up now" job — which, in finance, is most of them.

Cleaning the same export every month? Power Query records your steps once and replays them on next month's file. And if you're reconciling across several entities, that's the grind IMS Made Simple was built to end.