How to Use Flash Fill in Excel

Flash Fill watches the pattern in your first example and fills the rest of a column to match. Type one cleaned-up value, press Ctrl+E, and Excel infers the rule and applies it down the list. For analysts it is the quickest way to split names, pull codes out of strings, or recombine fields without writing a single text formula.

What Flash Fill does and when to use it

Flash Fill recognizes a pattern from the examples you type next to your source data and fills the remaining cells to follow it. It handles splitting one column into parts, joining several columns into one, and reformatting text such as changing case or inserting separators.

Reach for it when the transformation is regular and visual, like extracting the part before an underscore or building an email from a name. It is a one-off cleanup tool, so use it when you do not need the result to recalculate as the source changes.

Step by step

Suppose column A holds values like INV-2024-0098 and you want just the year in column B.

1. In the first cell of the output column, type the result you want, for example 2024.
2. Move to the cell directly below it.
3. Press Ctrl+E, or go to Data > Flash Fill.
4. Review the filled column; Excel applies the inferred pattern to every row.
5. If a row is wrong, correct it and press Ctrl+E again to refine the pattern.
6. Accept the suggestion shown, or press Esc to discard it.

One clear example is usually enough; add a second if the first guess is wrong.

A model use case

A general ledger export packs the account code, period, and description into one string. Type the cleaned account code beside the first row, press Ctrl+E, and Flash Fill pulls the code out of every row so you can build a clean lookup key for the model.

Going the other way, combine separate first and last name columns into a single display field, or stitch a region and product code into one identifier. It turns minutes of formula writing into a couple of keystrokes for one-time prep.

• Splits or combines text from a single typed example.
• Press Ctrl+E to trigger and again to refine after a correction.
• Results are static values, not live formulas.

Pitfalls and limits

Flash Fill produces fixed text, not formulas, so the output does not update when the source changes; you must run it again after edits. It also guesses, and an ambiguous pattern can fill plausible but wrong values, which is easy to miss in a long list, so always spot check.

It works best on consistent, well-structured text and struggles when rows vary in format. For anything that must recalculate or be reliably repeatable, use text functions such as LEFT, MID, TEXTSPLIT, or TEXTJOIN, or Power Query, which records the steps so they re-run on refresh.