Flash Fill for Pattern Recognition

Autofill extends patterns — sequences, formulas, series. It works with the structure of data.

Flash Fill recognises transformation patterns — splitting, combining, reformatting text. It works with the content of data.

Flash Fill watches what you type, recognises the pattern, and completes the rest of the column for you. It takes seconds, requires no formulas, and handles transformations that would otherwise require complex text functions like LEFT, MID, FIND, or CONCATENATE.

The Basic Workflow

• Add a new column next to your source data;
• Type the result you want for the first row (sometimes the second row too);
• Press Ctrl + E (Win) or control + E (Mac) — Flash Fill fills the rest instantly.

Practical Examples

Extract First Names

Our sales table has a Salesperson column (column E) with full names: John Miller, Sarah Lee, David Brown, and so on. For a reporting template, we need separate First Name and Last Name columns.

1. Insert a new column after column E — right-click column F header → Insert. Label it First Name in F1;
2. In F2, type John (the first name from E2);
3. Press Ctrl + E (Windows) and control + E (Mac).

Excel recognises you're extracting the first word from each name and fills the entire column: Sarah, David, Emily, Michael, Anna, Chris, Olivia — all the way down.

Extract Last Names

1. Insert another new column (right-click on the G header → Insert, label it Last Name in G1;
2. In G2, type Miller (the last name from E2);
3. Press Ctrl + E or control + E.

Combining Text

For example, your table has First Name in F and Last Name in G. You want a combined "Last, First" format in a new column.

Changing Case

Source data has JOHN MILLER in all caps. You want John Miller.

Extracting Parts of Codes

Your product column contains codes like LAP-East-001. You only need the region part (East).

In the new column, type East. Press Ctrl + E. Excel extracts the middle segment from every code in the column.

Reformatting Dates or Phone Numbers

Source: 02012026 → Target: 02.01.2026
Source: 0441234567 → Target: 044-123-4567.

In both cases: type the formatted version of the first entry, press Ctrl + E or control + E.

When Flash Fill Gets It Wrong

Flash Fill is pattern-matching, not logic — it can misread the pattern, especially when:

• Names have middle names or initials (Mary J. Clark → the "last name" extraction may grab J. instead of Clark);
• Values have inconsistent formatting in the source column;
• The pattern is ambiguous from a single example.

How to correct it:

• Type the correct value in the cell where Flash Fill went wrong;
• Press Ctrl + E/control + E again — Excel uses the correction to re-evaluate the entire column.

If the result is still wrong after two or three examples, the pattern may be too complex for Flash Fill. Switch to a text formula instead.

Combine into a new format:

• Add a column labelled Name (Last, First) e.g. column G.
• In the first data cell, type Miller, John. Press Ctrl + E/control + E.
• Confirm the format Last, First is applied consistently across all rows.