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

How-to basics · Updated November 21, 2026

Flash Fill in Excel recognizes a pattern in your examples and fills matching values into the rest of a column, such as extracting first names or reformatting phone numbers. In Excel for Microsoft 365 on Windows, enter an example beside your source data, then choose Data > Flash Fill or press Ctrl+E.

What Flash Fill Does

Flash Fill is a data-entry feature that uses examples to infer how you want text transformed. If a column contains full names and you type a first name in the next column, Excel may recognize that you want the first name from each corresponding row. It can also combine pieces of text, change the way text is formatted, or extract a consistent part of a value.

For example, if column A contains Maya Chen and column B contains Maya, Flash Fill may fill B with the first names for the remaining rows. If a column contains dates or phone numbers entered in different styles, it may also reproduce a consistent display pattern in a new column. The result is a set of ordinary cell values, not a formula that refers back to the original cells.

This makes Flash Fill useful for one-time cleanup and quick transformations. It is not a general-purpose rule engine: Excel guesses from the examples, and the guess may not reflect the rule you intended. Review the output before relying on it, especially when the source data is inconsistent.

How Flash Fill Recognizes a Pattern

Excel looks at the examples you provide and the nearby source data to infer a transformation. The feature is designed to recognize patterns in text, such as taking a name component, joining fields, or applying a consistent punctuation style. It does not require you to write a formula or choose a specific extraction function.

Often, one example is enough to trigger a suggestion. Two or more examples can make your intention clearer, particularly when a transformation could be interpreted in several ways. For instance, a value such as Alex Kim could be split into Alex and Kim, but a name with a middle name or a compound surname may make the desired result less obvious. Examples that show how those cases should be handled can help, though Excel still may not infer every exception.

Flash Fill generally works best when each row follows a recognizable pattern and the transformed value sits next to its source. It can sometimes use data from more than one neighboring column, but the more irregular the layout or values are, the less dependable its inference becomes. It does not understand the meaning of a field; it identifies patterns in the data.

How to Use Flash Fill

  1. Put the source values in a column, with one record per row. Leave a neighboring column available for the results.
  2. In the first result cell, type the output you want for the corresponding source row. For full names in A2:A20, for example, type the desired first name in B2.
  3. Move to the next cell in the result column and begin entering the next example if Excel has not already shown a suggested fill.
  4. Accept the suggested results if they match your intention. To run the feature manually, select the destination range or a cell in the destination column and choose Data > Flash Fill, or press Ctrl+E on Windows.
  5. Check the filled rows, including the bottom of the list and any unusual entries. Correct mistakes directly or provide clearer examples and run Flash Fill again.

Flash Fill can offer a preview as you type when automatic Flash Fill is enabled. Press Enter to accept a displayed suggestion. If Excel does not show one, the manual command is still available. Make sure the examples and source rows line up: an example entered on the wrong row can lead to incorrect output even if the pattern itself is sensible.

Practical Examples

For a list of full names, you might extract first names into a neighboring column, or create a separate column of initials. With names in a consistent format, Excel may also split out surnames. Check names with middle initials, hyphens, particles, or multiple spaces; these can produce results that do not follow your preferred convention.

Flash Fill can also help standardize text. If a column contains phone numbers and you enter a few examples in a preferred punctuation style, Excel may reproduce that style for other rows. Similarly, it may extract the part of an email address before the at sign, or combine a city and region from adjacent columns into a single text value. These are examples of pattern-based transformations, not guarantees that every format will be interpreted correctly.

It is also handy for preparing data for a mailing list or report. You can use it to create a display name from separate fields, change the order of name components, or remove a consistent prefix from a set of text values. Keep the original source columns until you have confirmed the results; that makes it easier to compare and correct the new values.

What Flash Fill Cannot Reliably Do

Flash Fill is not a formula and does not keep a live connection to the source. If you change a name in the original column after filling, the corresponding result does not automatically recalculate. Run Flash Fill again or update the result yourself if the source changes. This is an important difference from a formula-based transformation.

It is also a poor fit for data that requires complex rules, many exceptions, or ongoing updates. A list containing inconsistent name order, missing values, multiple formats, and special cases can lead to plausible-looking but incorrect results. Excel may apply the pattern you demonstrated literally without understanding why certain rows should be treated differently.

Flash Fill should not be treated as a data-validation or privacy feature. It transforms values locally in the workbook; it does not verify whether a phone number or address is real, and it does not anonymize sensitive information. Review the output and handle personal or confidential data according to your organization’s rules.

Common Problems and Fixes

  • No suggestion appears: Type one or two clear examples in the destination column, then use Data > Flash Fill or Ctrl+E. Confirm that automatic Flash Fill is enabled if you expect suggestions while typing.
  • The result follows the wrong pattern: Undo the fill if appropriate, clear incorrect results, and enter examples that make the intended transformation more explicit. Check that the examples are on the same rows as their source values.
  • Only some rows are filled: Select the relevant destination range before running the command. Look for blank rows, differently formatted records, or data breaks that may make the pattern harder to infer.
  • Results are wrong for exceptions: Correct those cells manually or handle the exceptions separately. A uniform pattern is easier to infer than a rule with many special cases.
  • The command is unavailable or behaves differently: Check which Excel app you are using. Flash Fill is a desktop Excel feature; Excel for the web may not offer the same command. Open the workbook in the desktop app when you need Flash Fill.

In Excel for Microsoft 365 on Windows, automatic suggestions can be controlled through File > Options > Advanced, under the Editing options setting for Automatically Flash Fill. If the setting is off, you can still try the manual Data > Flash Fill command. If the feature remains unavailable, confirm that the data is arranged in adjacent rows and that you are using a desktop version that includes Flash Fill.

Flash Fill Compared with Formulas and Power Query

Choose Flash Fill when you need a quick, one-time transformation and can inspect the results. It is often faster than writing a formula for a small cleanup task, but the output is static. A changed source value will not update the filled result automatically.

Use a formula when results need to update as source data changes, when the transformation must be repeatable, or when you need precise control over the rule. Formulas are also easier to audit in a workbook because their logic is visible in the cells. The right formula depends on the structure of the data and the Excel version, so test it on edge cases before filling it down.

Power Query is usually a better fit for repeatable imports and larger data-cleaning processes. It can record transformation steps that you can refresh when new source data arrives. That setup takes more effort than Flash Fill, but it is better suited to a workflow that must be rerun consistently. In short: use Flash Fill for quick, reviewed output; formulas for live cell-by-cell results; and Power Query for repeatable data preparation.

Availability and Platform Notes

Flash Fill was introduced in Excel 2013 for Windows and is included in modern desktop Excel versions, including Excel for Microsoft 365. Excel for Microsoft 365 for Mac also provides Flash Fill; use the Data tab’s Flash Fill command if a keyboard shortcut differs or is unavailable on your Mac. Excel for the web does not provide the same full desktop Flash Fill workflow, so open the workbook in desktop Excel when needed.

On Windows, Ctrl+E is the shortcut for Flash Fill. On a Mac, keyboard behavior can depend on the Excel version and keyboard configuration, so the Data tab command is the dependable route. Flash Fill does not require a Microsoft 365 subscription specifically if you have a desktop Excel version that supports the feature.

Frequently Asked Questions

How do I turn on Flash Fill in Excel?
On Windows, check File > Options > Advanced and look for Automatically Flash Fill under Editing options. You can also run it manually from Data > Flash Fill or press Ctrl+E, so automatic suggestions are not required to use the command.

Why is Flash Fill not working?
Excel may not recognize the pattern from the examples, automatic suggestions may be disabled, or you may be using Excel for the web. Enter clearer examples on the correct rows and try the desktop Data > Flash Fill command.

Does Flash Fill update when the original data changes?
No. Flash Fill writes values into the destination cells rather than formulas linked to the source. If the source changes, rerun Flash Fill or edit the result, or use a formula if you need a result that recalculates.

Can Flash Fill split first and last names?
It can often extract first names or surnames when the entries follow a consistent pattern. Names with middle names, compound surnames, or inconsistent ordering may need manual corrections or a more explicit method.

What is the shortcut for Flash Fill?
In Excel for Windows, press Ctrl+E. If you are using Excel for Mac and that shortcut does not work in your version, choose Data > Flash Fill from the ribbon.

Related

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.