Writing LEFT, MID, FIND, and PROPER formulas to clean imported data takes time. Flash Fill does the same work in seconds. Type one or two examples of the output you want in the column next to your source data, then press Ctrl+E. Excel recognises the pattern from your examples and fills the rest of the column. No formulas. No VBA. It handles name extraction, email construction, phone number standardisation, date reformatting, domain extraction, and case conversion. This guide covers the recognition logic, six real-world examples, and when to use formulas instead.
How Flash Fill Recognises Patterns
When you type an example in the column adjacent to your source data, Excel compares the typed text to the source cell in the same row. It looks for positional relationships (first word, text before "@", characters 5–8), case transformations, concatenation patterns, and date rearrangements. When it finds a consistent relationship, it suggests the fill as a greyed preview — press Enter to accept, or Ctrl+E to trigger and fill. Additional examples provide more context for ambiguous transformations.
Example 1: Split Full Names into First and Last Name Columns
This is the most common Flash Fill task. With a Full Name column in column A and an empty column B, type the first name from cell A2 in cell B2. Press Ctrl+E. Flash Fill fills first names for every row. Repeat in column C — type the last name from A2 in C2 and press Ctrl+E to fill all last names. No LEFT, FIND, or MID formula is required. Flash Fill handles compound last names, hyphenated names, and names with apostrophes correctly.
Example 2: Build Email Addresses from Name Columns
Flash Fill handles concatenation as naturally as extraction. With separate First Name (column A), Last Name (column B), and Domain (column C), type the first formatted email in column D — for example, "sarah.johnson@acme.com" if A2 is "Sarah", B2 is "Johnson", C2 is "acme.com". Press Ctrl+E. Flash Fill recognises the three-column concatenation, the lowercase transformation, the period separator, and the @ symbol. It generates every email address matching the format and case of your example exactly.
Example 3: Reformat Inconsistent Date Strings
Imported data from different systems often arrives with dates in mixed formats — "2025/01/15" from one database, "01-15-2025" from a US system, "Jan 15 2025" from a report export. Flash Fill can standardise all of them into a single display format from a single typed example. For data with multiple inconsistent formats in the same column, type one example for each distinct format before pressing Ctrl+E. The output is text, not a true Excel date value — use DATEVALUE() afterwards if date arithmetic is needed.
Example 4: Standardise Phone Numbers from Mixed Formats
Contact form data arrives with phone numbers in every format imaginable. Flash Fill recognises the numeric content and applies the target format from your example. Providing two or three examples helps when source formats vary significantly — each additional example handles more edge cases correctly. The output is text, not a numeric value, which preserves leading zeros and formatting characters like + and parentheses.
Example 5: Extract Email Domains for Segmentation
Flash Fill recognises the "text after @" pattern from a single example. With email addresses in column A, type just the domain from A2 in column B — for instance, "acme.com" if A2 is "sarah@acme.com". Press Ctrl+E. Flash Fill extracts the domain for every email address. The formula equivalent is =MID(A2,FIND("@",A2)+1,100). Flash Fill achieves the same result without any formula knowledge. Use the extracted domains for segmentation, de-duplication, or company matching.
Example 6: Convert ALL CAPS to Proper Case
Legacy system exports often arrive in ALL CAPS. Flash Fill converts them to Proper Case from a single typed example — the same result as the PROPER() function but without a permanent formula column. Type the first name in Proper Case in column B and press Ctrl+E. Flash Fill converts every row. This is particularly useful before importing contact data into a CRM or producing mail-merge documents. The output is static text — use PROPER() in a formula if the source column will continue changing.
Troubleshooting Flash Fill
Flash Fill fails in specific, predictable situations. All three issues below have clear causes and reliable fixes that take under a minute to apply.
Ctrl+E does nothing and Flash Fill is greyed out on the ribbon
First, check that Flash Fill is enabled: File > Options > Advanced > Editing Options > "Automatically Flash Fill". The feature must also be in a column immediately adjacent to the source column — no empty column between them is allowed. If the source data is inside an Excel Table, Flash Fill still works but the shortcut may behave differently. Try clicking Data > Flash Fill on the ribbon as an alternative to Ctrl+E to confirm the feature is active.
Flash Fill fills the column with incorrect or inconsistent values
A single example is often insufficient for ambiguous patterns. Provide two or three examples in consecutive rows before pressing Ctrl+E. Each additional example narrows the pattern interpretation. Include one example for each distinct format present in the source data. If Flash Fill still produces inconsistent results after three examples, the source data is too inconsistent for reliable pattern recognition. In that case, use a worksheet formula or Power Query transformation instead.
Flash Fill output does not update when source data changes
This is expected behaviour, not a bug. Flash Fill produces static text values — it does not create a live formula connection to the source column. When source data changes after Flash Fill runs, the output retains the original values. For data that changes regularly, use worksheet formulas like LEFT(), MID(), PROPER(), and TEXTJOIN() instead. These create live formula results that update automatically whenever the source changes.
Frequently Asked Questions
- What is the keyboard shortcut for Flash Fill?The keyboard shortcut is Ctrl+E on both Windows and Mac. Type your example in the cell adjacent to the source data, then press Ctrl+E. Alternatively, go to Data > Data Tools > Flash Fill on the ribbon. Flash Fill also triggers automatically as a greyed preview suggestion when you start typing — pressing Enter before typing a second character accepts the suggestion immediately without pressing Ctrl+E. If the automatic preview is distracting, disable it at File > Options > Advanced by unchecking "Automatically Flash Fill".
- Can Flash Fill handle multiple different source formats in the same column?Yes, up to a point. Provide one example for each distinct format before pressing Ctrl+E. For example, if a date column contains both "2025-01-15" and "Jan 15 2025", type one output example for each format in the first two rows of the output column, then press Ctrl+E. Flash Fill uses all examples to apply the correct transformation per row. For heavily inconsistent data with many different formats, Power Query M functions like Text.AfterDelimiter and DateTime.FromText provide more predictable and auditable results.
- What is the difference between Flash Fill and worksheet formulas?Flash Fill produces static text values in one operation — no formula, no ongoing calculation, no dependency on the source column after the fill runs. Worksheet formulas (LEFT, MID, PROPER, TEXTJOIN) produce dynamic values that recalculate automatically when source data changes, but they add permanent formula overhead and maintain a live link to the source. Use Flash Fill for one-time cleaning of imported or historical data that will not change. Use formulas when the output must stay synchronised with the source as new data is added over time.
- Does Flash Fill work inside an Excel Table?Yes. Flash Fill works on data inside Excel Tables, including in structured Table columns. Type your example in the adjacent Table column cell and press Ctrl+E. However, Flash Fill output cells in a Table column become static values — the Table does not auto-populate the Flash Fill pattern for new rows added later. For new rows to automatically receive the transformation, convert the Flash Fill output to a calculated Table column: delete the static values, then type the equivalent formula in the first data cell and Excel propagates it to all existing and future rows automatically.