Cellwise Start free

Excel guide · 6 min read

Flash Fill not working in Excel: why it fills nothing or the wrong thing

Quick answerIf Flash Fill does nothing, type one example in the column right next to your data, press Enter, then press Ctrl+E (or Data > Flash Fill). If the grey preview never appears on its own, turn on File > Options > Advanced > Automatically Flash Fill. If it fills the wrong pattern, type a second example on the row it got wrong and press Ctrl+E again. Flash Fill types fixed values, so for results that must update when the data changes, use a formula like TEXTBEFORE instead.

Still not working on your sheet? Paste your cells and the formula into Cellwise Fix. The engine shows which cause it is on your data, then you get the fix, checked. Open Cellwise Fix

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCD
1Full nameEmailFirst (Flash Fill)First (formula)
2Priya Natarajan[email protected]PriyaPriya
3Thomas O'Brien[email protected]TomThomas
4Anna Maria Rossi[email protected]AnnaAnna
5lee chen[email protected]leelee
6Mohammed Al Farsi[email protected]MohammedMohammed
7Jean-Luc Bernard[email protected]Jean-LucJean-Luc
8Grace[email protected]GraceGrace

Download this sheet as an Excel practice file (.xlsx)

Quick checks

Flash Fill (Excel 2013 and later) watches what you type in one column and copies the pattern down, using the data in the columns next to it. When it fails, it's almost always the layout, the examples, or the setting. The sample is a contact list where we want first names in column C.

  • Nothing happens while you type, but Ctrl+E works? The automatic option is off (fix 1).
  • Ctrl+E does nothing, or shows a message instead of filling? Check the layout (fix 2) and add an example (fix 3).
  • It filled, but some rows are wrong? Give a second example on a wrong row (fix 3).
  • It stopped partway down? Look for gaps in the source data (fix 4).
  • Results didn't change when you edited a name? That's by design (fix 5).
  • No Flash Fill button at all? Check your version (fix 7).

1. Turn on Automatically Flash Fill (or just press Ctrl+E)

There are two ways Flash Fill runs. Automatically: you type the first result, start typing the second, and Excel shows a grey preview of the rest of the column; press Enter to accept. Manually: you type one result and press Ctrl+E, or click Data > Flash Fill. Microsoft's own note says that if the preview doesn't appear, Flash Fill might not be turned on, and Ctrl+E or Data > Flash Fill still runs it.

So if the preview never shows up, don't wait for it. Press Ctrl+E. To get the automatic preview back, switch the option on.

  1. File > Options > Advanced.
  2. Under Editing options, tick Automatically Flash Fill.
  3. Click OK. The preview now appears when you start typing your second example.

2. Put the example column right next to the data

Flash Fill reads the cells beside the one you're typing in. If your example column has an empty column between it and the names, or you typed the example on a different row from the source, there's nothing for it to learn from. In the sample, the first names go in column C, directly beside the names and emails, starting on row 2, the first data row.

Also check that you typed the example in the first data row, not the header row, and that you pressed Enter before Ctrl+E. With the cursor still in the cell (Edit mode), the ribbon command isn't available.

  1. Insert a column directly to the right of the source: right-click the column letter > Insert.
  2. In row 2, type the result you want for that row (Priya) and press Enter.
  3. With the next cell selected, press Ctrl+E.

3. Give it a second example when the pattern is ambiguous

Flash Fill guesses from very little. One example of "Priya" from "Priya Natarajan" could mean "the first word", "the first five letters" or "everything before the space". Messy rows expose the guess: Anna Maria Rossi, a lowercase "lee chen", a single name like Grace. When a row comes out wrong, type the right value over it and press Ctrl+E again; each correction teaches it more.

Always scan the results. Flash Fill doesn't warn you when a row is wrong. Watch especially for two-word surnames: "Mohammed Al Farsi" is a problem for any rule. The "last word" rule gives Farsi, and "everything after the first space" gives Al Farsi, which is right here but wrong for "Anna Maria Rossi". Decide which rule fits your data, then check those rows by eye.

=TEXTAFTER(A6," ",-1)

Result: Farsi

Farsi: the last word only, so a two-word surname loses its first part. Flash Fill can make the same choice.

What does "everything after the first space" give?

For Mohammed Al Farsi it keeps the whole surname, which is why there is no single right rule for every name list.

=TEXTAFTER(A6," ")

Result: Al Farsi

Al Farsi.

4. Gaps and mixed data stop it early

If the column beside your examples has blank rows, Flash Fill can stop at the gap or fill the rows after it oddly. Check the rows just after any gap. If you only need part of a list, select the cells you want filled first, then press Ctrl+E, so Excel fills exactly those cells.

It also helps to clean the source first: stray spaces, notes in brackets and mixed formats make the pattern harder to spot. A TRIM column or Find and Replace pass before Flash Fill saves a lot of corrections.

5. Flash Fill results don't update: use a formula when they must

Flash Fill types plain values, not formulas. In the sample, Flash Fill wrote "Tom" in C3, then someone corrected the name in A3 to Thomas O'Brien. C3 still says Tom, and nothing flags it. For a one-off clean-up that's fine. For a list that keeps changing, a formula stays in step.

In Excel 365 and Excel 2021 or later, TEXTBEFORE gets the first name. Its last argument says what to return when there's no space, so a single name like Grace comes back whole instead of an #N/A error.

=TEXTBEFORE(A8," ",,,,A8)

Result: Grace

Grace: there's no space in A8, so the formula returns the whole name instead of #N/A.

How do I find rows where Flash Fill is out of date?

Compare the static column with a live formula and count the differences.

=SUMPRODUCT(--(C2:C8<>D2:D8))

Result: 1

1: one row (Tom vs Thomas) no longer matches its source.

What if I don't have TEXTBEFORE?

LEFT and FIND work in every version. Wrap it in IFERROR for names without a space.

=IFERROR(LEFT(A3,FIND(" ",A3)-1),A3)

Result: Thomas

Thomas: the text before the first space in "Thomas O'Brien".

Can a formula fix the case too?

Yes. Wrap it in PROPER.

=PROPER(TEXTBEFORE(A5," "))

Result: Lee

Lee: "lee chen" comes back as Lee.

6. When Flash Fill is the right tool, and when it isn't

Flash Fill is great for a quick, one-time job: splitting names in an export you'll send today, pulling initials, reformatting phone numbers, or joining columns into "Last, First". It's the wrong tool when the source data will change, when you need an audit trail of how a value was made, or when someone else will maintain the sheet and can't see the "rule". Use the formula methods in separate first and last names and split text by delimiter for those.

7. No Flash Fill at all

Flash Fill arrived in Excel 2013 for Windows. Excel 2010 and earlier don't have it, so use the formulas above. Current versions of Excel for Mac and Excel for the web have it under Data > Flash Fill, and Ctrl+E is the shortcut. If the Data tab command is there but dimmed, you're probably still editing a cell: press Enter or Esc first.

Common mistakes

Waiting for the grey preview

If it doesn't appear, press Ctrl+E. Turn on Automatically Flash Fill in File > Options > Advanced to get the preview back.

Leaving a blank column between the data and the examples

Type the examples in the column directly beside the source data.

Trusting every row

Scan for middle names, single names and two-word surnames, and correct one wrong row to retrain it.

Using Flash Fill on data that will change

Its results are fixed values. Use TEXTBEFORE, TEXTAFTER or LEFT and FIND so the column updates.

FAQ

Why is Flash Fill not working in Excel?

Usually the example column isn't next to the data, the pattern is ambiguous, or the automatic option is off. Type an example beside the data, press Enter, then Ctrl+E, and add a second example if rows come out wrong.

How do I turn on Flash Fill?

File > Options > Advanced, then tick Automatically Flash Fill under Editing options. You can always run it manually with Ctrl+E or Data > Flash Fill.

Does Flash Fill update automatically when data changes?

No. It writes values once. Run it again after changes, or use a formula such as TEXTBEFORE that recalculates.

Can I undo Flash Fill?

Yes, press Ctrl+Z straight after it runs to remove the filled values.

Functions used

Related guides

Updated