Flash Fill Not Working? 6 Fixes That Get Ctrl+E Going

When Flash Fill refuses to work, the cause is almost always one of six things: the feature is disabled, you are relying on the automatic suggestion instead of Ctrl+E, your target column is not adjacent to the data, your examples are inconsistent, number formatting is confusing the pattern detection, or the transformation is genuinely too complex for Flash Fill. Work through these fixes in order — the first three solve the vast majority of cases.

Fix 1: Is Flash Fill enabled in Excel Options?

Flash Fill can be switched off entirely, in which case neither the automatic preview nor Ctrl+E does anything. Re-enable it in the options — both checkboxes matter, one for the automatic suggestions and one for the feature itself.

File → Options → Advanced → Editing options
  ☑ Enable AutoComplete for cell values
  ☑ Automatically Flash Fill

After enabling, restart the workbook if Ctrl+E still seems dead. If you are on Excel 2010 or earlier, stop here — Flash Fill only exists from Excel 2013 onward.

Fix 2: Why does the automatic preview never appear?

The grey ghost-text preview only shows up while you are typing the second example, and it is easily suppressed by pauses, edits, or certain data shapes. Do not rely on it. Type one or two examples, select the next empty cell in that column, and press Ctrl+E (or Data → Flash Fill). Manual triggering works in many cases where the automatic preview never fires, and it is the standard workflow described in our Flash Fill guide.

Fix 3: Does Flash Fill require an adjacent column?

Yes. Flash Fill only reads from a contiguous block of data — your example column must be directly next to the source columns, with no fully blank column in between. If you type examples in column F but the data lives in columns A–C, Ctrl+E reports "We couldn't find a pattern". Move your output column so it touches the data range, or delete the empty separator columns. A column that merely looks empty but contains formatting is fine; a truly empty column breaks the link.

Fix 4: Are your example entries consistent?

Flash Fill infers one rule from your examples, so two examples that follow different rules produce garbage or "no pattern" errors. The classic trap is names: from "John Smith" you type "Smith, John", but for "Mary Ann Jones" you type "Jones, Mary Ann" while Flash Fill inferred "middle word is the surname". Give it the tricky cases as examples, not just the easy ones:

Source              Your examples (type 2–3, include a hard case)
John Smith          Smith, John
Mary Ann Jones      Jones, Mary Ann     ← teach the multi-word case
Bob Lee             (press Ctrl+E here)

Also check for stray leading/trailing spaces in the source and inconsistent capitalization — Flash Fill treats "IBM " and "IBM" as different shapes.

Fix 5: Is number formatting sabotaging the pattern?

Flash Fill works on the displayed text, not the underlying value. Dates displayed as 05/01/2024 versus stored serial numbers, phone numbers with formatting applied, or numbers showing rounded decimals all mislead it. Two specific traps: extracting digits from ZIP codes drops leading zeros (Flash Fill outputs a number, and 07030 becomes 7030), and extracting "years" from dates returns inconsistent results when the column mixes real dates and text dates. Fix: pre-format the output column as Text before pressing Ctrl+E when the result should keep leading zeros, and normalize the source column to one consistent format first.

Fix 6: When should I give up and use formulas or Power Query?

Flash Fill output is static text — it does not update when the source changes, and it cannot express conditional logic ("if the value contains X, do Y"). If you re-run the same cleanup every week, or the rule has branches, switch tools. For live formulas, TEXTBEFORE, TEXTAFTER, MID, and friends in our Excel text functions guide cover the same splits and joins dynamically. For repeatable multi-step cleanups on imported files, Power Query records the transformation once and replays it on refresh — the right tool the moment "not working" really means "not the right feature".

' Flash Fill alternative that stays live:
=TEXTAFTER(A2," ")&", "&TEXTBEFORE(A2," ")   ' "John Smith" → "Smith, John"

What does "We couldn't find a pattern" actually mean?

It means Flash Fill compared your examples against the adjacent data and could not derive a single consistent rule. In practice: too few examples (give it 2–3), inconsistent examples (Fix 4), no adjacent data (Fix 3), or a transformation that depends on meaning rather than position — Flash Fill understands "third word" and "text before the @", never "the surname" or "the city". Rephrase your examples so the rule is positional and it will click.