Flash Fill in Excel: Ctrl+E Examples and When It Fails

Flash Fill automatically detects patterns in your data and fills remaining cells. It's like magic—no formulas needed for splitting names, formatting text, or combining columns.

Quick answer: Flash Fill (Excel 2013+) fills a column by learning from your examples. Type the result you want in the cell next to your data, press Ctrl+E, and Excel applies the pattern to every row. It's ideal for one-time cleanups like splitting names or reformatting phone numbers — but the results are static text, not formulas, so they don't update when the source data changes.

How do I use Flash Fill in Excel?

Type one or two examples of the output you want in the column directly next to your source data, then press Ctrl+E (or Data tab → Flash Fill). Excel infers the pattern from your examples and fills the rest of the column instantly. If the first attempt is wrong, press Ctrl+Z, add another example, and try again.

Method 1: Keyboard Shortcut
1. Type one or two examples
2. Press Ctrl + E

Method 2: Menu
1. Type examples
2. Data tab → Flash Fill

Method 3: Auto-detect
Start typing the next example — Excel shows a
gray preview of the whole column. Press Enter to accept.

How do I split full names with Flash Fill?

Type the first name from one row's example into the adjacent column, press Ctrl+E, and Flash Fill splits every other row the same way.

Column A (Full Name) → Column B (First Name)
John Smith           → John
Jane Doe             → Type "Jane"
Bob Johnson          → Press Ctrl+E

Excel fills: Bob, Alice, etc. for all rows

How do I combine two columns with Flash Fill?

Type the combined result of one row's two columns into the next column, press Ctrl+E, and Flash Fill merges every remaining row the same way.

Column A    Column B    → Column C (Combined)
John        Smith       → John Smith
Jane        Doe         → Type "Jane Doe"
Bob         Johnson     → Press Ctrl+E

Result: All names combined automatically

How do I reformat phone numbers with Flash Fill?

Type one phone number in the target format next to the raw digits, press Ctrl+E, and Flash Fill applies the same punctuation pattern to the rest of the column.

Column A         → Column B (Formatted)
5551234567       → (555) 123-4567
5559876543       → Type "(555) 987-6543"
5552468135       → Press Ctrl+E

All numbers formatted consistently

How do I extract part of a text string with Flash Fill?

Type the exact substring you want (a domain, a city, a code) for one row, press Ctrl+E, and Flash Fill extracts the matching piece from every other row.

Extract Email Domains

Column A                  → Column B (Domain)
[email protected]           → gmail.com
[email protected]             → Type "yahoo.com"
[email protected]       → Press Ctrl+E

All domains extracted

Extract from Addresses

Column A (Full Address)              → Column B (City)
123 Main St, Boston, MA 02108        → Boston
456 Oak Ave, Seattle, WA 98101       → Type "Seattle"
789 Elm Rd, Austin, TX 78701         → Press Ctrl+E

How do I reformat dates with Flash Fill?

Type one date written out the way you want it (e.g. "January 15, 2024"), press Ctrl+E, and Flash Fill reformats the rest of the column to match.

Column A         → Column B (Formatted)
1/15/2024        → January 15, 2024
2/20/2024        → Type "February 20, 2024"
3/10/2024        → Press Ctrl+E

All dates reformatted to long format

How do I add a prefix or suffix with Flash Fill?

Type one ID with the prefix or suffix attached (e.g. "EMP-001"), press Ctrl+E, and Flash Fill adds the same text to every value in the column.

Column A (ID)    → Column B (Full ID)
001              → EMP-001
002              → Type "EMP-002"
003              → Press Ctrl+E

Result: EMP-001, EMP-002, EMP-003, ...

Can Flash Fill clean messy data?

Yes — type the cleaned-up version of one cell (trimmed spaces, fixed capitalization), press Ctrl+E, and Flash Fill applies the same cleanup pattern to the rest of the column.

Remove Extra Spaces

Column A              → Column B (Cleaned)
  John   Smith        → John Smith
  Jane    Doe         → Type "Jane Doe"
   Bob  Johnson       → Press Ctrl+E

Capitalize Properly

Column A         → Column B (Title Case)
john smith       → John Smith
JANE DOE         → Type "Jane Doe"
bob johnson      → Press Ctrl+E

How do I extract just the numbers from text with Flash Fill?

Type the digits-only version of one cell (e.g. "12345" from "Order #12345"), press Ctrl+E, and Flash Fill pulls the matching numbers out of the rest of the column.

Column A                 → Column B (Numbers Only)
Order #12345             → 12345
Invoice-67890            → Type "67890"
Ref: 11111               → Press Ctrl+E

Why does Flash Fill silently fail or give wrong results?

Flash Fill guesses the pattern from as few as one example, so with inconsistent data it can guess wrong without any warning — it fills the whole column with confident-looking but incorrect values. Always spot-check rows that differ from your examples: middle names, missing values, different lengths, or unusual formats are where it breaks.

Inconsistent source data: "John Smith" and "Smith, John" in the same column confuse the pattern
Middle names and suffixes: an example from "John Smith" mishandles "Mary Jane Watson Jr."
Blank cells in the source: Flash Fill may stop at the gap or skip rows
Numbers that look like patterns: extracting "digits" from mixed text can grab the wrong group
Non-adjacent column: your examples must be directly next to the source data
Feature disabled: check File → Options → Advanced → "Automatically Flash Fill"

If Ctrl+E does nothing or says it "couldn't see a pattern", see the full checklist in Flash Fill not working: fixes.

Is Flash Fill better than formulas?

Flash Fill is faster to apply but produces static text — it does not recalculate when source data changes, and it can't be reused on next month's file. Formulas (or Power Query) are slower to write but stay live and repeatable. Use Flash Fill for one-off cleanups and formulas for anything recurring.

Use Flash Fill When:	Use Formulas When:
One-time data transformation	Data updates regularly
Pattern is complex to code	Need dynamic updates
Quick ad-hoc cleaning	Reusable process
Importing external data once	Live calculations

Formula equivalents of common Flash Fill jobs:

First name:      =TEXTBEFORE(A2, " ")        (365)
                 =LEFT(A2, FIND(" ", A2)-1)  (older)
Combine names:   =A2 & " " & B2
Email domain:    =TEXTAFTER(A2, "@")         (365)
Proper case:     =PROPER(A2)
Trim spaces:     =TRIM(A2)

In Excel 365, dynamic array functions like TEXTSPLIT and TEXTBEFORE/TEXTAFTER cover most Flash Fill jobs with live formulas, and Power Query is the right tool when the same cleanup runs every week.

What tips help Flash Fill succeed?

Give it 2-3 examples for complex patterns, keep the examples in a column directly adjacent to the source data, and undo (Ctrl+Z) and add another example if the first attempt guesses wrong.

Provide 2-3 examples for complex patterns
Examples must be in an adjacent column
Works best with consistent patterns
If it doesn't work, try more examples
Undo (Ctrl+Z) and try different examples if needed

What are Flash Fill's limitations?

Flash Fill only exists in Excel 2013 and later, produces static values that don't update automatically, and struggles with source data that isn't consistently patterned.

Doesn't update automatically (not a formula)
Need to re-run if source data changes
Can't handle highly inconsistent patterns
Only available in Excel 2013 and later

Pro Tip: Flash Fill is perfect for one-time data cleaning tasks. For recurring transformations, use formulas or Power Query. Always verify Flash Fill results—it's smart but not perfect!

← Back to Excel Tips

Related Tips

Written by Aaron Pitt · Useful Data Tips