Flash Fill: Ctrl+E Filled Sixteen Rows, Thirteen Were Right, and £7,579.00 Left the Report Without Leaving the File

The file was sixteen remittance lines, one supplier code hidden inside each reference, and a report that had to say how much each of six suppliers had paid in the quarter.

The person doing it typed 4471 beside the first row, 4472 beside the second, pressed Ctrl + E, and got a filled column of sixteen account codes in about a second. Then a SUMIFS against a list of the six supplier codes, and the report was done:

Four of the six lines are right to the penny. Two are not, by £4,770.25 and £2,808.75, and the report as a whole is £7,579.00 short of the file it was built from — 11.94% of the money, gone, with no error value, no blank cell and no zero anywhere on the sheet.

Nothing in Excel went wrong. Flash Fill did exactly what it is documented to do: it looked at two examples, found a rule that produced both of them, and applied that rule to the other fourteen rows. The rule it found was consistent with everything it had been shown. It was also wrong about three rows nobody showed it.

What this covers. Flash Fill arrived in Excel 2013 for Windows and is in Excel 2016 and later for Mac, in Microsoft 365, and in Excel for the web. The shortcut is Ctrl + E; the menu is Data ▸ Flash Fill; and the fill-as-you-type behaviour is governed by File ▸ Options ▸ Advanced ▸ Automatically Flash Fill. TEXTBEFORE, TEXTAFTER, TEXTSPLIT and UNIQUE in the formula sections need Microsoft 365 or Excel 2021; the MID/FIND/LEN versions beside them work in every version back to the nineties. Account codes in a remittance reference are only the example: order numbers in an email subject, a cost centre inside a filename, a postcode at the end of an address, a part number in a description — every one of them is the same job, and every trap below applies unchanged.

1) The Rule Excel Learned, and the Rows That Were Not Invited

Flash Fill was given two examples:

GB-4471-HALDEN-FOODS-Q3       → 4471
GB-4472-MARROW-AND-TATE-Q3    → 4472

At least two different rules produce both of those outputs from both of those inputs:

• the delimiter rule — take the text between the first hyphen and the second; and
• the position rule — take four characters starting at character four.

Both are consistent with everything Flash Fill was shown. Neither can be eliminated by the evidence, because the evidence is two rows that are the same shape. Flash Fill picks one — it ranks candidate programs and takes the best-scoring one — and it does not tell you which, because there is nowhere in Excel to look at the rule Flash Fill inferred. There is no formula bar for it. The cells contain their answers and nothing else.

On this file it took the position rule, and you can read the consequences straight off the prefixes:

Every two-letter prefix survives. Every three-letter prefix does not, and it does not fail loudly — it returns -881, a four-character string that sits in the column looking exactly as much like an account code as its neighbours.

Four numbers to hold on to:

=SUM(D2:D17)                              → 63,500.00   everything that arrived
=COUNTA(UNIQUE(TEXTBEFORE(B2:B17,"-")))   → 4           country prefixes in the file
=COUNTIF(B2:B17,"IRL-*")                  → 3           rows of a shape nobody trained on
=SUMIF(E2:E17,"-881",D2:D17)              → 7,579.00    money filed under a code that does not exist

🎯 Scenario: Before you build anything on a Flash Filled key, put =COUNTA(UNIQUE(TEXTBEFORE(B2:B17,"-"))) — or on older Excel, =SUMPRODUCT(1/COUNTIF(...)) over the prefixes, or simply sort the column and look — in a spare cell. If the file has more shapes in it than you showed Flash Fill, you already know the answer is wrong; the only open question is by how much.

2) Flash Fill Is Induction, Not Instruction

This is the distinction the whole article turns on, and it is easy to miss because Flash Fill feels like giving an instruction.

When you write =MID(B2,4,4) you have stated a rule. It is visible, it is auditable, somebody can read it in six months and disagree with it. When you type 4471 and press Ctrl+E you have shown an example and asked Excel to work out what rule you had in mind. Those are opposite directions. One goes rule → answers. The other goes answers → rule → more answers, and the middle step is invisible.

That middle step is a real piece of program synthesis, and it is good at its job. Given examples that pin the rule down, it finds the rule. Given examples that do not, it finds a rule — one consistent with what it saw — and it is under no obligation to pick the one you meant, because you never said what you meant.

So the quality of a Flash Fill is not a property of Flash Fill. It is a property of the examples you gave it. And the examples people give are, almost without exception, the first two rows of the file, which is to say the two rows that happen to be at the top, not the two rows that disagree with each other.

🎯 Scenario: Sort or filter the column before you train Flash Fill, find the row that is least like the others — the longest one, the shortest one, the one with an extra delimiter — and type that row's answer as one of your examples. Two examples that differ teach a rule. Two examples that match teach a shape.

3) Why the Three Bad Rows Looked Like One Good Category

Here is the part that turned a data error into a reporting error.

IRL-8811-VERITY-LABS-Q3 and IRL-8812-KESTREL-GROUP-Q3 are different references for different suppliers with different codes. Characters 4 to 7 of both of them are -881. The rule is positional, the prefix is three letters on both, and the two codes agree on their first three digits, so all three offending rows — L03, L09 and L13 — produced the same output.

=COUNTIF(E2:E17,"-881")     → 3          rows
=SUMIF(E2:E17,"-881",D2:D17)→ 7,579.00   pounds
=COUNTA(UNIQUE(E2:E17))     → 7          distinct codes extracted
=COUNTA(SupplierMaster)     → 6          suppliers that exist

Three broken rows that each produced a different piece of junk would have looked like breakage. Three broken rows that produce the same piece of junk look like a category. -881 has a count, a total, a share of the file — 11.94% — and every property a real supplier code has except existing.

And because it was one category rather than three anomalies, it landed in the report as a single clean subtraction rather than as noise. The two affected suppliers did not go to zero, which is what would have happened if every one of their rows had broken; they went to part of themselves, because Verity Labs appears in this file with an IRL- prefix twice and an IE- prefix once, and Kestrel Group with one of each. The surviving rows kept the lines alive and plausible.

🎯 Scenario: After any Flash Fill of a key, put the extracted column on a PivotTable's Rows and count it. Compare the number of distinct values to the number of things that exist. Seven codes against a master file of six is a two-second check, and it is the check that nobody runs because the column looked filled.

4) Why Nothing Errored

There are four separate reasons this file could not go red, and they are worth naming because each one removes a defence you might have assumed you had.

• The extraction cannot fail. -881 is a perfectly legal four-character string. Taking characters 4 to 7 of a 23-character reference is not an error condition in any sense Excel recognises.
• SUMIFS does not audit. =SUMIFS($D$2:$D$17,$E$2:$E$17,$G2) asks "how much is filed under 4471" and answers correctly. It has no opinion about the rows filed under something else. A criteria list of six codes reports six numbers whether the source contains six codes or six hundred.
• The lines still foot to the total. If the report's own total row is =SUM() over its six lines, it agrees with them to the penny — £55,921.00 — because the lines and their total come from the same six filters. Self-consistency is not accuracy, and this report is perfectly self-consistent.
• Nothing is blank. Every cell in the extracted column has a value. The failure state of Flash Fill on this file is not emptiness, it is confidence.

The report never claimed to be the file. It claimed to be the total for six codes, and it was. Nobody wrote down the sentence "these six codes are all the codes", so nobody could check it.

🎯 Scenario: Every report built on a filtered or keyed subset gets one extra cell: the unfiltered total of the source, and the difference. =SUM(D2:D17)-SUM(ReportLines) is one cell, it costs nothing, and on this file it would have read 7,579.00 on the day it was built.

5) The PivotTable Would Have Told You Immediately

This is the most useful practical point in the article, and it is about the difference between two questions.

SUMIFS against a list of codes asks: how much is in these six buckets? A PivotTable with the extracted code on Rows asks: what buckets are there? The second question is strictly stronger, and on this file it answers itself:

The pivot's grand total is £63,500.00 — the whole file — because a pivot reports what it finds rather than what it was asked for. The row that should not exist sorts to the top, because a leading hyphen sorts before digits.

That is the whole diagnosis, visible without a single formula, in the time it takes to drag one field. The SUMIFS version of the same report cannot produce this row, ever, because a criteria list is a claim about what exists and SUMIFS treats claims as instructions.

🎯 Scenario: The first thing you do with a derived key column — Flash Filled, formula-built, pasted, whatever — is put it on a pivot by itself with a count and a sum, and read the list of values from top to bottom. Not to build a report. To find out what you actually made.

6) Flash Fill Is a Photograph

Three weeks after the report went out, somebody fixed the source. L13's reference was corrected from IRL-8812-KESTREL-GROUP-Q3 to IE-8812-KESTREL-GROUP-Q3, because the Irish entity had been set up with two country codes and the cleanup had finally reached it.

The extracted column did not change. It still said -881.

This is not a bug and it is not a refresh problem. Flash Fill does not write a formula. It writes values — literal text in cells, with no reference to the cell it was derived from, no dependency in the calculation chain, and no memory of the rule that produced it. Nothing about that cell knows that B14 exists. Recalculating the workbook cannot help, because there is nothing to recalculate; Refresh All cannot help, because there is no query; and reopening the file cannot help, because the values are simply what is in the file now.

A formula column has exactly one advantage over Flash Fill, and it is this one:

=TEXTBEFORE(TEXTAFTER(B14,"-"),"-")   corrects itself the moment B14 changes
Flash Filled value                     corrects itself never

The same applies inside an Excel Table. Typing a formula into one cell of a Table column fills the whole column and keeps filling it as rows are added. Flash Filling a Table column fills it once, as values, and new rows come in blank.

🎯 Scenario: Ask one question before pressing Ctrl+E — will this source ever change? If the answer is no, and it genuinely is no for a one-off cleanup of a file you are about to throw away, Flash Fill is the right tool and this whole article is inapplicable. If the answer is yes, or "I don't know", you are choosing a photograph over a rule, and the photograph will be right for exactly as long as nothing moves.

7) Where the Fill Quietly Stops

Flash Fill works on a contiguous block, and that has consequences people meet as "it only filled some of it" long after the fact.

• A blank row ends the block. If row 9 of the source column is empty, Flash Fill sees the data as ending at row 8 and fills to row 8. The top of the column looks filled. It is filled. It is just not finished, and the rows below it are empty in a way that a SUMIFS will happily treat as "not one of my six codes".
• It needs to be adjacent. The column you are filling has to touch the data it is learning from. Leave a spare column between them and Ctrl+E has nothing to look at.
• It may fill some rows and not others. Where Flash Fill cannot make a row fit the rule, it can leave that cell empty rather than guess. This is the good outcome — visible, countable — and it is why a =COUNTBLANK() over the filled column is worth one cell.
• It has no opinion about hidden or filtered rows. What you can see is not what it read.

=COUNTA(E2:E17)     → 16   how many cells got a value
=COUNTBLANK(E2:E17) → 0    how many did not

On this file both answers are reassuring and both are irrelevant, which is the point: a complete fill and a correct fill are different properties, and only one of them is easy to check.

🎯 Scenario: Immediately after Ctrl+E, press Ctrl + ↓ from the top of the filled column and look at where you land. If it is not the last row of your data, Flash Fill stopped early, and it stopped at a blank you have not noticed yet.

8) Numbers and Dates Come Out as Text

Everything Flash Fill produces is text, because it is assembled from pieces of text. That is fine for a code and consequential for anything you intend to add up or sort.

=SUM(E2:E17)                 → 0          sixteen populated cells, and a zero
=ISNUMBER(E2)                → FALSE
=XLOOKUP(E2,Master!A:A,...)  → #N/A       text "4471" against numeric 4471
=VALUE(E2)                   → 4471       the fix, one column over

The #N/A there is the friendly failure — it is the version of this problem that announces itself, and if the report in this article had been built with XLOOKUP against a numeric master file it would have gone red on every row instead of being quietly light by £7,579.00. Text keys are a nuisance; silent text keys that happen to match are the expensive case.

Dates behave the same way. A Flash Filled 06/07/2026 is thirteen characters, not the number 46209, and it will sort between 05/12/2025 and 07/01/2024 exactly as text does. Watch for leading zeros in both directions: Flash Fill preserves 00471 as text, which is usually what you wanted, and a later VALUE() or Text to Columns will quietly turn it into 471.

🎯 Scenario: After a Flash Fill that is meant to produce numbers, select the column and read the status bar. If Sum is missing and only Count is shown, every value in it is text. That is a one-glance check that needs no cell at all.

9) The Formula That Says What It Means

The fix for this file is one formula, and it is not more complicated than the problem:

=TEXTBEFORE(TEXTAFTER(B2,"-"),"-")        → 4471, 8811, 5510, 8812 — all sixteen rows

TEXTAFTER(B2,"-") takes everything past the first hyphen — 4471-HALDEN-FOODS-Q3 — and TEXTBEFORE of that stops at the next one. The prefix can be two letters, three letters or seven; the rule does not care, because the rule is about the delimiter and not about where the delimiter happens to sit. Two equivalents:

=INDEX(TEXTSPLIT(B2,"-"),2)               the second hyphen-delimited token
=MID(B2,FIND("-",B2)+1,
     FIND("-",B2,FIND("-",B2)+1)-FIND("-",B2)-1)    every version of Excel ever shipped

The MID/FIND version is ugly, and its ugliness is honest: those two nested FINDs are the price of saying "between the first hyphen and the second" in a language from 1985. What matters is that all three of them state a rule that a reader can disagree with, and that all three of them are still true next month.

For anything that arrives on a schedule — a bank file every week, an export every night — neither Flash Fill nor a formula column is really the answer. Power Query is: Data ▸ From Text/CSV, split the column by delimiter, and the split is part of the query, so next week's file gets the same treatment without anybody opening it. Flash Fill is a keystroke, a formula is a rule in a cell, and a query is a rule that survives the file being replaced.

🎯 Scenario: Run both. Put the Flash Filled column and the TEXTBEFORE/TEXTAFTER column side by side and compare them. On this file that comparison finds three rows in one cell (section 11), and it costs you the ten seconds it took Flash Fill to be wrong.

10) When Flash Fill Is the Right Tool

None of the above makes Flash Fill a bad feature. It is a genuinely remarkable one, and there is a clean line between the jobs it is right for and the jobs it is not.

Use it when the work is one-off and disposable — a list somebody pasted into an email, a column you are reshaping before pasting it somewhere else, a quick look at whether a source is even parseable. Use it when the transformation is easier to demonstrate than to describe: reformatting Smith, John Q. into John Smith is four nested functions and one example. Use it to explore an unfamiliar file, where the fill breaking on row 12 is the fastest way to discover that row 12 is different.

Do not use it for a key — anything you will join, look up, group or sum on. Do not use it for anything feeding a report somebody else reads. Do not use it on a file that will be refreshed, re-imported or corrected. And do not use it for a transformation you cannot state in a sentence, because if you cannot state the rule, you certainly cannot check that Excel guessed it.

🎯 Scenario: The line is not "Flash Fill versus formulas", it is disposable versus durable. If the result of the fill will still be in a cell when somebody makes a decision from it, the rule that produced it needs to be in a cell too.

11) Four Checks

Four things, in this order, before a Flash Filled column is allowed to be a key:

1. Count the shapes first. =COUNTA(UNIQUE(TEXTBEFORE(B2:B17,"-"))) → 4. One number, before the fill, that says whether the file is as uniform as the two rows you are about to train on.
2. Read the output, not the fill. =COUNTA(UNIQUE(E2:E17)) → 7 against a master file of 6, and =COUNTIF(E2:E17,"*-*") → 3 for codes containing a character no code contains. A pivot on the column does both at once.
3. Reconcile to the source. =SUM(D2:D17) against the sum of the report's lines. £63,500.00 against £55,921.00 is £7,579.00, and a report that cannot account for 11.94% of its own input is not finished.
4. Extract it twice, two different ways. The code is also at the end of the Bank Narrative, after REF . So =TEXTAFTER(C2,"REF ") in a spare column and then:

=SUMPRODUCT(--(E2:E17<>F2:F17))   → 3   rows where two independent extractions disagree

Two routes to the same value that agree are evidence. Two routes that disagree name the rows. This is the strongest check on the list and the one that needs no knowledge of what the right answer is.

🎯 Scenario: Keep check 4 in the workbook rather than running it once. One spare column, one SUMPRODUCT, and a cell that reads 0 until the day somebody adds a row in a new shape — at which point it reads 1, next to the report, where it can be seen.

12) Twelve Traps

1. Training on the first two rows. They are the top of the file, not a sample of it. This article is entirely this trap.
2. Two examples that share a shape. They cannot distinguish a delimiter rule from a positional one, and you will not be told which was chosen.
3. The inferred rule is not visible anywhere. No formula bar, no dialog, no audit trail. The only way to find out what Flash Fill did is to look at what it produced.
4. Values, not formulas. Corrected source, unchanged output, forever. New rows in a Table come in blank.
5. Everything is text. SUM returns 0, XLOOKUP against numeric keys returns #N/A, and dates sort alphabetically.
6. A blank row ends the block. Flash Fill fills to the gap and stops, and the top of the column looks finished.
7. The fill column must be adjacent to the source. A spare column in between and Ctrl+E has nothing to learn from.
8. Your typo becomes the pattern. A trailing space in one of your examples is a trailing space on all sixteen rows, and "4471 " does not match "4471" in a lookup.
9. Silent case normalisation. Give it Halden Foods as the example for HALDEN FOODS LTD and you may get proper case across the column — which turns IBM into Ibm and plc into Plc.
10. Automatic Flash Fill firing unasked. With the Advanced option on, a suggested fill appears as you type and Enter accepts it. People accept fills they never requested and never looked at.
11. Partial fills that look complete. Where it cannot fit a row, Flash Fill may leave the cell empty; =COUNTBLANK() over the column is the only thing that notices.
12. Merged cells and filtered views. Merged cells in the source block break the fill outright, and a filtered sheet does not mean Flash Fill read only the visible rows.

What to Take Away

Flash Fill is inference, and inference is only as good as the examples behind it. Two rows that look alike do not narrow anything down; they hand Excel a set of rules that all fit, and one gets chosen without a record of which. That is not a defect — it is what "work out what I mean from an example" has to mean — and it is survivable, as long as you remember that you have asked a question rather than given an instruction.

The damage on this file came from the three things that make a wrong guess expensive rather than annoying. The wrong output was well-formed, so nothing errored. It was identical across all three bad rows, so it looked like a category instead of a fault. And it was consumed by a SUMIFS against a hand-written list of codes, which is a tool that answers the question you asked and never mentions the question you should have asked.

Three habits cover all of it. Train Flash Fill on the rows that disagree, not the rows at the top. Put any derived key on a pivot by itself and read the list of values before building anything on it. And reconcile the report to the unfiltered source in one cell — because £63,500.00 minus £55,921.00 is a subtraction that takes two seconds and would have caught every word of this article on the day it happened.