Flash Fill in Excel: Is There a Formula or Shortcut?

Flash Fill in Excel is not a formula: it detects a pattern in your example and fills matching values into nearby cells. In Excel for Microsoft 365 on Windows, select the first result cell and press Ctrl+E, or choose Data > Flash Fill; use a formula instead if the result must update when its source changes.

What Flash Fill does

Flash Fill helps transform or combine text without requiring you to write a formula. You show Excel one or more examples of the result you want, and Excel looks for a pattern in the neighboring data. It can, for example, pull first names from full names, combine initials, remove punctuation from telephone numbers, or build email addresses from names and a company domain.

The feature was introduced in Excel 2013 and is available in current desktop versions, including Microsoft 365 for Windows. Its usefulness comes from recognizing a pattern rather than applying a named rule that you define. That makes it quick for repetitive cleanup, but it also means Excel can misunderstand examples when the source data is inconsistent or the intended transformation is ambiguous.

One important distinction: Flash Fill places ordinary values in the destination cells. It does not put a formula there, and it does not create a live link to the source cells. If you later edit a name in the original list, a Flash Fill result based on that name will not necessarily change with it.

How to use Flash Fill in Excel

Put the source data in one column and leave the adjacent column available for the results. The first example is your instruction to Excel. Make sure it clearly shows the transformation you want, especially if the source values do not all follow the same format.

Enter the desired result beside the first source value. For example, if A2 contains “Maya Chen,” type “Maya” in B2.
Move to the next blank result cell, such as B3. If Excel has not already filled the remaining cells, press Ctrl+E on Windows.
Review the proposed results, including entries farther down the list. Correct any wrong or incomplete values before using the output.
Alternatively, select the destination range and choose Data > Flash Fill. Depending on the selection and Excel’s recognition of the pattern, it may fill more than the active cell.

You can also type a second example before invoking Flash Fill. Two examples are often helpful when a single example could fit several interpretations. For instance, provide two correctly formatted telephone numbers if you want Excel to remove parentheses, spaces, and hyphens but preserve the digits. If the results look wrong, undo the fill with Ctrl+Z, supply a clearer example, and try again.

On Excel for Mac, use Data > Flash Fill if the command is available in your version; the shortcut can depend on the keyboard setup. Excel for the web may not offer the same desktop Flash Fill command in every environment. If you cannot find it, open the workbook in the desktop app or use a formula or Power Query for a repeatable transformation.

Practical examples and pattern clues

Flash Fill is most effective when each row follows a predictable pattern and the desired output is visible in the data. These examples illustrate common tasks:

Extracting a first name: From “Maya Chen,” enter “Maya” in the next column, then ask Flash Fill to complete the list.
Building an email address: If the source has “Maya Chen” and the intended address format is maya.chen@example.com, enter that complete address as an example. Check carefully for duplicate names, accents, or exceptions to the company’s naming convention.
Standardizing phone numbers: Show a result with the desired punctuation or with punctuation removed. Confirm that leading zeroes remain intact; telephone numbers are identifiers, not quantities to calculate.
Combining text: If separate columns contain a first and last name, enter a correctly spaced full name in a third column, then use Flash Fill to repeat the pattern.
Reformatting dates: Flash Fill may recognize a date written in a different style, but dates can be ambiguous across regional settings. Check both the displayed result and whether Excel is treating it as text or a date value.

Some examples that look alike to a person may have different meanings to Excel. A list containing “Ann Lee,” “Ann-Marie Lee,” and “Lee, Ann” does not have one obvious first-name rule. Provide examples that cover the variations, or use a formula or a more controlled data-cleaning method.

Is there a Flash Fill formula?

No. There is no worksheet function named Flash Fill, and pressing Ctrl+E does not insert a formula. If your goal is to extract a first name and you want a formula that recalculates when the original text changes, use a text formula instead. With full names in A2:A100 and the first name for each row to go in column B, enter this in B2:

=LEFT(A2,FIND(" ",A2)-1)

For example, if A2 contains “Maya Chen,” the result is “Maya.” This formula assumes a space separates the first and last names and that the first name comes first. It can return an error if A2 contains no space, and it is not a complete solution for every naming convention. Fill the formula down to apply it to the other rows.

With the same source layout—full names in A2:A100 and surnames in column C—a simple formula for the text after the first space is:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

For “Maya Chen,” the result is “Chen.” This also assumes exactly the kind of spacing and name structure the formula expects. Unlike a Flash Fill result, either formula recalculates when A2 changes. For more complicated data, consider whether names contain middle names, multiple spaces, suffixes, or family names with more than one word before choosing an extraction rule.

Flash Fill results, source changes, and saving

Because Flash Fill returns values rather than formulas, it is useful when you want a one-time transformation or a cleaned column to pass to another system. It is less suitable when the source data will keep changing and the output must remain synchronized. In that case, formulas can update automatically, while Power Query can be useful for repeatable imports and transformations.

If you correct a source row after using Flash Fill, do not assume the corresponding output will refresh. You can run Flash Fill again after updating the examples and source data, but review the new results because the pattern may be interpreted differently. If the results are final, you can copy the output and use Paste Special > Values when you need to make the values independent of any other formulas in the workbook. Flash Fill’s output is already values, so this extra step is only relevant if you are copying results that came from formulas.

Excel has an option to fill patterns automatically as you type. In desktop Excel for Windows, check File > Options > Advanced for the “Automatically Flash Fill” setting under the editing options. When enabled, Excel may complete a column after it recognizes your example. You can still invoke Flash Fill manually with Ctrl+E, and you can turn the automatic behavior off if it interrupts your work.

Accuracy and common problems

Flash Fill makes a prediction; it does not verify that the prediction matches your business rules. A column can look mostly correct while a few rows have been interpreted differently. Review the entire output, especially where the inputs contain exceptions, repeated names, different punctuation, missing values, or inconsistent spacing.

Nothing happens: Confirm that a usable example is beside the source data, that the destination cells are available, and that you are using a version with the feature. Try Data > Flash Fill instead of the shortcut.
The pattern is wrong: Undo the result, add another representative example, and invoke Flash Fill again. Avoid giving examples that could be explained by several different rules.
Some rows are blank or unusual: Check for blank source cells and exceptions to the pattern. Decide whether those rows should stay blank or receive a special result.
Leading zeroes disappear: Excel may interpret an entry as a number rather than text. For identifiers such as phone numbers or postal codes, use an appropriate text format and inspect the results before sharing them.
The source changes but the output does not: That is expected for Flash Fill values. Re-run the fill or replace it with a formula or another repeatable transformation.

Flash Fill is not a substitute for checking sensitive or high-impact data. For large lists, compare the transformed column against the source, filter for blanks or unexpected formats, and keep an untouched copy of the original data until the results have been checked.

Flash Fill compared with formulas and other tools

Choose the method based on how the workbook will be used, not just how quickly the first result appears. Flash Fill is convenient for a one-off pattern that is easy to demonstrate. A formula is better when the output should respond to edits in the source or when the rule needs to be visible and repeatable in the worksheet. Formulas also make it easier for another user to inspect the logic, though they require you to express that logic correctly.

Power Query is worth considering when the same cleanup must be repeated whenever data is imported or refreshed. It provides transformation steps that can be reapplied, which is more controlled than relying on Flash Fill to infer a pattern each time. For a small, one-time list, Power Query can be more setup than necessary. For a recurring workflow, the extra setup may reduce repeated manual work.

Flash Fill can also help create a quick preview of a transformation before you decide how to implement it permanently. Still, if the pattern affects many records, feeds reports, or must be applied consistently by a team, document the rule and validate the output rather than treating a successful fill as proof of accuracy.

Frequently Asked Questions

What is the shortcut for Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, the shortcut is Ctrl+E. You can also use Data > Flash Fill. On Mac, use the ribbon command if the shortcut differs or is unavailable in your keyboard setup.

Why is Flash Fill not working?
Excel may not recognize the pattern from the example, the destination may not be beside the source data, or the feature may not be available in the app you are using. Add a clearer example and try Data > Flash Fill in desktop Excel.

Does Flash Fill update automatically when source cells change?
No. Flash Fill writes values, not live formulas, so a later edit to the source does not automatically update the filled result. Run Flash Fill again or use a formula if the output must recalculate.

Can Flash Fill split first and last names?
Yes, it can often do so when the names follow a consistent pattern. Enter the desired first-name or surname result beside an example, invoke Flash Fill, and check rows with middle names, compound surnames, or unusual spacing.

Is Flash Fill available in Excel for the web?
Availability can differ from desktop Excel. If you do not see Flash Fill in the web app, open the workbook in desktop Excel or use a formula or Power Query for the transformation.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.