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.
- Put the source values in a column, with a heading if useful. Keep the neighboring destination column empty below its heading.
- 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.
- If the transformation is not obvious, enter a second correct example in B3. Two examples can help Excel distinguish between possible patterns.
- 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.
- 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
- How to Name the Inventory Range in Excel (Step-by-Step)
- Remove a Range Name in Excel: Step-by-Step Guide
- How to Use Absolute References in Excel for Mac
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Keep Track of Vacation Days in ExcelCreate a simple Excel vacation tracker with start and end dates, leave type, approval status, and a list…
- Microsoft Excel Power Query: What It Does and How to Use ItMicrosoft Excel Power Query is a built-in tool for importing data and transforming it into a clean, repeatable…
- How to Add Headers in Excel on SharePoint and Format ThemTo add column headers to an Excel workbook stored in SharePoint, open the workbook for editing, enter a…
- Excel Column Size: Width, Pixels, and How to Change ItIn Excel, column size is set as a width measured in character-based units, not as a fixed number…
- Excel Shortcut to Jump Between Sheets: Keys and TipsIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next worksheet and Ctrl+Page…
- Create a Shared Distribution List in Outlook: Team OptionsTo create a list just for yourself, open Outlook’s People or Contacts view and create a contact list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.