Flash Fill in Excel: How It Works and When to Use It

Flash Fill in Excel automatically completes a column when it recognizes a pattern in examples you provide. In Microsoft 365 for Windows, type the first result beside your source data and press Ctrl+E, or choose Data > Flash Fill; check the results because Flash Fill creates fixed values, not formulas.

What Flash Fill does

Flash Fill is a data-entry feature for turning existing text or numbers into a different arrangement without writing a formula. You show Excel the kind of result you want, and it tries to apply that pattern to the remaining rows. It is especially useful for small, one-time transformations, such as splitting names, joining text, extracting part of an address, or standardizing how a phone number is displayed.

For example, a list might contain full names such as Maya Chen and Luis Ortega in one column. If you type Maya beside the first name and Luis beside the next, Excel may infer that you want the first name from each full name. Flash Fill can then fill the rest of the destination column. You can also use it to combine separate first- and last-name columns, provided you demonstrate the desired spacing and order.

The feature is not a formula engine that understands the meaning of your data. It identifies a pattern from your examples and generates results that resemble that pattern. That distinction matters: a correct-looking preview is useful, but it does not prove that Excel has interpreted every unusual row correctly.

How to use Flash Fill on Windows

Arrange the source data so the example output can go in an adjacent, empty column. A header row is fine; start entering examples on the same rows as the corresponding records. Then follow these steps:

Click the first empty cell beside the first record you want to transform.
Type the result exactly as it should appear. For a full name in A2, for example, type the first name only in B2.
Press Enter. If the pattern is not obvious, type a second example in B3 as well.
Press Ctrl+E, or select Data > Flash Fill on the ribbon.
Review the filled cells, including the first and last records and any rows with unusual data.

Excel may display a preview of the proposed entries while you type. If that preview matches your intention, pressing Enter can accept it; Ctrl+E is the direct command for applying Flash Fill after entering a pattern. If nothing happens, the pattern may not be clear enough, the destination may not be positioned beside the source, or the command may be unavailable in that Excel version.

To undo an unwanted fill immediately, use Ctrl+Z on Windows. You can also select the generated cells and delete them, taking care not to remove the original source data. Flash Fill does not create a separate transformation rule that you can edit later.

Practical examples of patterns

Flash Fill can handle many straightforward text transformations. The examples below show the kind of demonstration to give it; the result depends on the exact content and consistency of the source column.

Split names: From Maya Chen, enter Maya in the first-name column and Chen in the last-name column. These are separate patterns, so fill each destination column separately.
Combine fields: If first names are in one column and surnames in another, type a complete name with a space between the parts, then invoke Flash Fill in the output column.
Extract a domain: From an address such as sam@example.com, enter example.com as the desired output. Check rows with subdomains or different address formats carefully.
Reformat phone text: If source entries have consistent digits, demonstrate the desired punctuation and spacing in the output. Verify that no digits were omitted or moved.
Create a code from text: You could demonstrate initials or a short identifier based on a product name. Because the intended rule may be ambiguous, inspect the generated entries rather than assuming Excel knows the business rule.

Examples should be representative, not merely convenient. If some names include middle names, some email addresses have subdomains, or phone numbers include country codes, include examples of those variations before relying on the fill. A pattern inferred from ordinary rows may not correctly cover exceptional ones.

How Excel infers a pattern

Flash Fill compares the source values with the examples you enter and looks for a repeatable transformation. One example may be enough for an obvious task, such as copying the first word from a two-word name. Two or more examples often help when the transformation could be interpreted in different ways. For instance, a single output might look like a fixed value rather than evidence that you want a particular part of a longer string.

Consistency improves its chances. If names are separated by spaces in most rows but some use commas, or if dates and codes appear in several formats, Excel has less uniform evidence to follow. Where possible, provide several examples that demonstrate the exceptions, and inspect the output for records that do not match the main pattern.

Flash Fill is not a substitute for defining a precise rule. If an identifier must always use the first three letters of a department and the last four digits of an employee number, a formula or a controlled data-cleaning process may be safer than asking Excel to infer that rule from examples. Use Flash Fill where a quick transformation is acceptable and the resulting values can be checked.

Important limitations

The main limitation is that Flash Fill writes ordinary values into cells. It does not retain a live connection to the source cells. If you change a source name later, the previously generated first name does not automatically recalculate. Run Flash Fill again or use a formula or query-based workflow if the output needs to stay synchronized with changing input.

It can also make a plausible but incorrect guess. Consider the names Ann Marie Jones and Ann Lee. A requested first name could mean Ann in both cases, or Ann Marie for the first person if the organization treats that as a compound given name. A few examples cannot reliably settle a rule that the data itself does not make clear. Similar ambiguity occurs with abbreviations, inconsistent punctuation, and numbers embedded in text.

When the output represents numbers, consider whether you need a number or text. A phone number, postal code, or account code is often an identifier, not a quantity, and leading zeros can be important. Check that the result preserves them. Also review cell formatting and the actual contents of generated cells: displaying a value in a particular style is not the same as preserving every character of an identifier.

Common problems and fixes

Ctrl+E does not fill anything: Add a second example, make sure the destination is beside the source data, and try Data > Flash Fill. A highly irregular source may not provide a recognizable pattern.
Some rows are wrong: Look for inconsistent separators, extra spaces, middle names, blank cells, or different formats. Correct those entries manually or use a method with an explicit rule.
The result stops partway down: Check for blank rows or gaps that may interrupt the data range, then select the intended destination cells and run Flash Fill again if appropriate.
Excel fills automatically when you are typing: In Windows, go to File > Options > Advanced and look under Editing options for Automatically Flash Fill. You can clear that checkbox to stop automatic suggestions; the ribbon command can still be used manually.
The output no longer matches changed source data: That is expected because Flash Fill produces values rather than a continuing formula. Reapply it after changes, or choose a dynamic approach for recurring updates.

Before replacing or deleting source data, keep an unchanged copy of the original column until you have validated the result. This is particularly important for contact lists, customer records, and identifiers, where a single altered character can make a record unusable.

Flash Fill compared with formulas and other tools

Choose Flash Fill for quick, one-time cleanup when you can readily check the results. It is fast and requires no formula knowledge, but its output is static and the inferred rule is not visible in the cells. That makes it less suitable for a workbook that will receive new records regularly or needs a transformation someone else can audit.

A formula is preferable when the rule should be explicit and update as source cells change. Formulas can be copied down a column and recalculated when inputs change, though they take more effort to construct and maintain. If the task is simply dividing text at a consistent delimiter, Text to Columns may offer a more direct, repeatable command: select the source column and choose Data > Text to Columns, then follow the wizard. It is designed for splitting data, not for every kind of text rearrangement.

For repeated imports or multi-step cleanup, Power Query is often a stronger option. It records transformation steps that can be applied again when refreshed data arrives, rather than leaving only one-time values in the worksheet. The right choice depends on whether the task is occasional or recurring, whether the rule must be transparent, and how costly an unnoticed error would be.

Availability on Mac and the web

Flash Fill is available in supported desktop versions of Excel, including Microsoft 365 for Windows. On Windows, the ribbon route is Data > Flash Fill, and Ctrl+E is the keyboard shortcut. Excel for Mac also provides Flash Fill in supported desktop editions; use Data > Flash Fill if the shortcut differs or is unavailable in your setup. Keyboard behavior can depend on the Mac keyboard configuration and Excel release.

Excel for the web may not expose the same Flash Fill command as the desktop app. If you do not see it in the ribbon, open the workbook in desktop Excel to perform the operation. A workbook that already contains filled values can still be viewed or edited in a browser, but those values will not become dynamic merely because the file is opened online.

Frequently Asked Questions

What is the shortcut for Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+E after entering an example pattern. You can also choose Data > Flash Fill. On Mac, use the ribbon command if the shortcut does not work in your configuration.

Why is Flash Fill not working?
Excel may not have enough examples to infer the pattern, or the source data may be inconsistent. Type another representative result, check that the destination is beside the source column, and try Data > Flash Fill. If the command is missing, open the workbook in a supported desktop version.

Does Flash Fill update when the original data changes?
No. Flash Fill writes results as values, so they do not recalculate when you edit the source. Reapply Flash Fill after changes, or use formulas or Power Query if you need repeatable updates.

Can Flash Fill split first and last names?
Yes, when the names follow a pattern Excel can infer. Enter a first-name example in one output column and a surname example in another, then run Flash Fill for each. Review names with middle names, compound names, or inconsistent punctuation.

Is Flash Fill the same as Text to Columns?
No. Flash Fill infers a transformation from examples and can rearrange or combine text. Text to Columns splits a selected column using delimiters or fixed-width positions. Use the tool that matches the structure of the data and check the result either way.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.