Excel Flash Fill: Automatically Fill Repeating Patterns

How-to basics · Updated November 18, 2026

Yes. Excel can automatically use Flash Fill when it recognizes a pattern in the values you enter: type an example beside your source data, then accept the suggested results when they appear. You can also run it yourself with Ctrl+E on Windows or by choosing Data > Flash Fill.

What Flash Fill does

Flash Fill takes a pattern from examples you provide and fills similar results into the cells below or beside them. It is useful for transforming text without writing a formula. For instance, if a column contains full names, you can enter a person’s first name in the adjacent column and ask Excel to extract first names for the remaining rows.

Other common jobs include combining parts of a name, making usernames from names, separating a code from a longer string, or changing how text is capitalized. Flash Fill can also help format text consistently, such as turning 5551234567 into 555-123-4567. The exact result depends on the example and the surrounding data, so review the filled values before relying on them.

Flash Fill is available in modern desktop versions of Excel, including Excel for Microsoft 365. It is a pattern-recognition feature, not a worksheet function: you do not enter a formula, and there is no Flash Fill formula to recalculate later.

Turn automatic Flash Fill on or off

In Excel for Microsoft 365 on Windows, automatic Flash Fill is controlled by an option. To check it, open File > Options > Advanced. Under Editing options, select Automatically Flash Fill to enable automatic suggestions, or clear the checkbox to turn them off. Select OK to apply the change.

When the option is enabled, enter a result in a cell next to the source data. If Excel recognizes the pattern, it may show a preview for the other rows. Press Enter to accept the suggestion. If no preview appears, you can still run Flash Fill manually; automatic recognition is not guaranteed for every dataset.

On Mac, use Data > Flash Fill to run the feature. The Windows checkbox path above is specific to Excel for Windows; the settings available on Mac can differ by Excel version. In Excel for the web, feature availability and behavior can also differ from the desktop app, so use the desktop command if Flash Fill is not available in your browser version.

Run Flash Fill manually

Manual Flash Fill is useful when Excel does not offer a preview or when you want to decide exactly when the transformation happens. On Windows, the keyboard shortcut is Ctrl+E. On Mac, use the ribbon command Data > Flash Fill if the shortcut is unavailable or differs for your keyboard setup.

  1. Put the source values in a column, with a heading if useful. Keep the neighboring destination column empty below its heading.
  2. In the first destination cell, type the result you want for the first source row. For a full name in A2, for example, type that person’s first name in B2.
  3. If the transformation is not obvious, enter a second correct example in B3. Two examples can help Excel distinguish between possible patterns.
  4. Select the destination cells where you want the results, including the examples if appropriate, and choose Data > Flash Fill. On Windows, you can instead press Ctrl+E.
  5. Check the output row by row. Correct any wrong results before using the data in a report, import, or other process.

Flash Fill generally uses the nearby column or columns as clues. Keep the source data aligned by row, and avoid blank rows or unrelated values that might confuse the pattern. If the command fills an unexpected range, undo immediately with Ctrl+Z on Windows. On Mac, use Command+Z.

Examples of useful patterns

Suppose column A contains full names such as Maya Chen and Jordan Lee. Typing Maya in B2 and Jordan in B3, then running Flash Fill, can create a first-name column. To extract last names instead, enter Chen and Lee as the examples. If you want a combined display name from separate first- and last-name columns, type the desired combined form in the destination column and use the same process.

For a simple email pattern, imagine that columns A and B contain first and last names and your organization uses first.last@example.com. Type one complete address in the destination column, then provide another example if needed and run Flash Fill. This can be convenient for preparing draft values, but it does not verify that an address exists, that a user account is active, or that the organization’s naming convention has no exceptions.

Flash Fill can also help with text cleanup. If a source column contains codes like INV-2026-0042 and you want only the final number, enter 0042 as an example and see whether Excel identifies the intended part. This kind of extraction can be ambiguous: a later code might have a different length or structure. Inspect edge cases rather than assuming the first example describes every row correctly.

How Flash Fill differs from formulas

Flash Fill writes the results as ordinary cell values. They are not linked to the original cells. If you later change a source name, an extracted first name created with Flash Fill does not automatically change with it. To refresh the transformation, run Flash Fill again or use a formula or another repeatable data-cleaning method.

A formula is often a better choice when source data changes regularly, when the transformation must remain traceable, or when the rule needs to be applied consistently to new rows. Flash Fill is often quicker for a one-time cleanup or a small, visually checkable transformation. Unlike a formula, it does not show a rule that a coworker can inspect in the formula bar, so document the transformation if the resulting values will be reused.

Flash Fill is also different from AutoFill. AutoFill extends a series or copies cell contents, such as continuing a date sequence or copying a formula down a column. Flash Fill infers a text pattern from examples. The names sound similar, but they solve different tasks.

Common problems and how to fix them

  • No preview appears: Confirm that automatic Flash Fill is enabled on Windows, then try Data > Flash Fill or Ctrl+E. Add a second clear example if one example could have several interpretations.
  • Some rows are wrong: Look for inconsistent source formats, middle names, suffixes, extra spaces, or exceptions to the pattern. Correct the examples, rerun the command, and check the unusual rows individually.
  • Leading zeros disappear: A value such as 0042 may be interpreted or displayed as a number rather than text. If the result must preserve zeros as text, inspect the output and set up the destination appropriately before using it elsewhere.
  • The result uses the wrong part of a value: Excel may have inferred a different rule than you intended. Provide more representative examples, particularly where the source strings vary in structure, or use a formula or Power Query for a rule that needs explicit control.
  • Later edits do not carry through: That is expected because Flash Fill creates static values. Rerun it after changes or switch to a formula-based approach if the output must update automatically.

When to choose another method

Choose Flash Fill when the transformation is straightforward, the data is reasonably consistent, and you can review the output. Choose a formula when results need to update with the source or when the transformation should be visible and repeatable. For larger, recurring imports with several cleanup steps, Power Query may be more suitable because it records transformation steps that can be applied again when the data is refreshed.

For a small list, manual editing may be safest if every row has a different exception. For a large or important dataset, do not treat a plausible-looking fill as proof that every result is correct. Keep the original column, compare a sample including unusual entries, and preserve a copy of the original workbook before making substantial changes.

Frequently Asked Questions

How do I make Excel Flash Fill automatically?

On Windows, go to File > Options > Advanced and select Automatically Flash Fill under Editing options. Then type an example beside the source data and press Enter if Excel shows a suggested fill. You can run the feature manually if it does not suggest one.

What is the shortcut for Flash Fill?

In Excel for Windows, the shortcut is Ctrl+E. On Mac, choose Data > Flash Fill if a keyboard shortcut is unavailable or does not match your setup.

Why is Flash Fill not working?

Excel may not recognize the pattern, automatic Flash Fill may be disabled, or the source values may be inconsistent. Enter a second example and run Data > Flash Fill manually. If the output is still unreliable, use a formula or another method with an explicit rule.

Does Flash Fill update when I change the original data?

No. Flash Fill produces static values, not formulas. Changing a source cell does not automatically update its corresponding Flash Fill result. Run Flash Fill again or use a formula if the result needs to stay connected to its source.

Can Flash Fill split names into separate columns?

Yes, it can often extract first or last names when you provide examples beside the full-name column. Names with middle names, suffixes, or different ordering can make the pattern ambiguous, so check those rows carefully.

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.