To use Flash Fill automatically in Excel for Microsoft 365 on Windows, type an example of the result you want in the column beside your source data, then press Enter when Excel shows a preview. You can also run it yourself with Ctrl+E or Data > Flash Fill; it recognizes a pattern and fills matching results without creating a formula.
What Flash Fill does
Flash Fill is an Excel feature for turning existing text or values into a new pattern. For example, it can extract first names from full names, combine parts of an address, or format phone numbers consistently. You show Excel an example, and it tries to apply the same transformation to the remaining rows.
It is different from AutoFill, which extends a series or copies a formula, and from a formula, which calculates a result from cell values. Flash Fill writes ordinary values into cells. It does not leave a formula behind to recalculate when the source changes. That makes it quick for a one-time cleanup, but important to remember when the source data is likely to be edited later.
How Excel recognizes a pattern
Flash Fill looks at examples you provide and infers the text transformation they have in common. If column A contains full names and you type the first person’s first name in column B, Excel may recognize that you want the first name from each row. It can also handle transformations that would take several text operations to express, such as combining a name with a particular punctuation style.
The feature is making a pattern-based guess, not understanding the meaning of a field. One example may be enough when the desired result is unambiguous. If there are multiple plausible interpretations, give it another example. For instance, a list of people with middle names may make it unclear whether you want the second word or the final word. Different examples can clarify the intended rule, but you should still review the resulting column.
Use Flash Fill step by step
- Put the source data in a worksheet column, with a heading in the first row if appropriate. Keep the rows you want to process together.
- In the neighboring empty column, type the desired result for the first data row. If A2 contains Jordan Lee, you might type Jordan in B2.
- Press Enter. If Excel displays a preview of results for the other rows, check that the suggestions follow your intended pattern.
- Press Enter again to accept the preview. If no preview appears, type a second correct example in the next row, then press Ctrl+E or choose Data > Flash Fill.
- Review the filled cells, especially the first and last rows and any records that do not follow the usual format.
To run Flash Fill directly, place the active cell in the destination range and use Ctrl+E on Windows, or select Data > Flash Fill. The ribbon command is useful when the automatic preview does not appear. Flash Fill generally works best when the source and destination columns are adjacent and the records are arranged in corresponding rows.
Common jobs and concrete examples
Flash Fill is most useful for repetitive text cleanup where you can describe the desired output with a few examples. Common uses include:
- Splitting names: turn Jordan Lee into Jordan in one column, or Lee in another. Test records with middle names and suffixes separately.
- Combining fields: turn a first name and last name in separate columns into a display name such as Jordan Lee, or create a consistent label from a code and description.
- Extracting part of an email address: from jordan.lee@example.com, produce jordan.lee. Check addresses with different formats, plus signs, or subdomains before relying on the output.
- Standardizing punctuation or spacing: convert differently formatted phone-number text into a consistent visual style, provided Excel identifies the pattern correctly.
- Reformatting identifiers: create a presentation-friendly version of a text code. Confirm that the result preserves any leading zeroes that matter.
Flash Fill can also be helpful when a transformation is awkward to explain as a formula. However, a quick result is not proof that every row was interpreted correctly. If a source column contains inconsistent spacing, different name orders, or several address formats, inspect exceptional records and consider cleaning or separating those cases first.
Automatic filling and settings
Automatic Flash Fill means Excel may show a suggested fill while you enter a pattern in a neighboring column. If it does not appear, you can still invoke the feature with Ctrl+E or Data > Flash Fill. On Windows, the automatic behavior can be checked under File > Options > Advanced, in the Editing options, using the Automatically Flash Fill setting. The label or availability can vary across Excel editions.
In Excel for Mac, use Data > Flash Fill when the command is available; menu and shortcut details can differ by version, so the ribbon command is the clearest route. Flash Fill is a desktop Excel feature and is not available in Excel for the web in the same way. If you are using the browser version and cannot find the command, open the workbook in the desktop app or use an appropriate formula instead.
Limits and common problems
Flash Fill is convenient, but it is not a general-purpose data-cleaning engine. It may fail to show a preview if the sample does not make the intended pattern clear, if the destination is not positioned as expected, or if the data has too many exceptions. Pressing Ctrl+E can trigger a fill attempt, but it cannot make an ambiguous pattern reliable.
- No suggestion appears: enter a second example, check that the destination column is next to the source, and try Data > Flash Fill.
- Some rows look wrong: correct the examples so they demonstrate the desired rule more clearly, run Flash Fill again, and inspect irregular records individually.
- Leading zeroes disappear: codes that Excel treats as numbers may lose formatting. Store identifiers as text or check the cell format and final values before using the results.
- Dates are interpreted unexpectedly: date order and display can depend on regional settings. If the source is text or uses mixed date formats, verify the output rather than assuming Excel has understood the intended date.
- New source rows are not included: Flash Fill creates a one-time result, not a live connection. Run it again for new rows or use a formula or another repeatable process.
After accepting a fill, treat the results as data that needs checking. Sorting or filtering the source and destination together helps preserve row relationships. If you later sort only one column, the values can become detached from the records they came from.
Flash Fill compared with formulas
Choose Flash Fill when you need a quick, static transformation and a few examples produce a dependable result. Choose a formula when results must update as source cells change, when the workbook will receive new rows regularly, or when the rule needs to be visible and auditable. Formulas also make it easier to apply exactly the same logic to a changing dataset.
For example, with full names in A2:A20 and each name consisting of a first name, one space, and a last name, this formula in B2 returns the first name: =TEXTBEFORE(A2," "). This formula in C2 returns the text after the first space: =TEXTAFTER(A2," "). For the sample Jordan Lee, the results are Jordan and Lee. Copy the formulas down for the remaining rows. These functions are available in Microsoft 365 and Excel 2024; names containing middle names or extra spaces need a more carefully defined rule.
Flash Fill does not require you to know a text function, which is one reason it is approachable. A formula is usually the better choice when repeatability matters. In a shared workbook, another person can inspect a formula and see how its result depends on the source cell; a Flash Fill result alone does not explain how it was produced.
Alternatives for repeatable cleanup
For a small one-off task, Flash Fill may be the shortest route. For recurring work, consider options that preserve a repeatable process. Formulas are useful when the transformation is straightforward and should update automatically. Excel’s Text to Columns command can split data consistently by a delimiter or fixed width; find it under Data > Text to Columns. It is a more explicit choice when every row uses the same separator, such as a comma or space.
Power Query can be a better fit for larger or recurring imports because you can define transformation steps and refresh the query when new source data is provided. It takes more setup than Flash Fill, but the steps are reusable. The right choice depends on how often the task will recur, how consistent the input is, and whether another user needs to review or repeat the process.
Frequently Asked Questions
What is the shortcut for Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+E to run Flash Fill. You can also choose Data > Flash Fill. On Mac, use the Data tab command if available because keyboard shortcuts can differ by version.
Why is Flash Fill not working?
Try entering two clear examples, placing the destination beside the source data, and choosing Data > Flash Fill. Also check that the automatic Flash Fill setting is enabled on Windows. Review the input for inconsistent patterns that might make the desired result ambiguous.
Does Flash Fill update when I change the original data?
No. Flash Fill inserts static values rather than formulas. If source data changes, run Flash Fill again or use a formula or repeatable data-cleanup process that recalculates the result.
Can Flash Fill split first and last names?
Yes, when the names follow a consistent pattern. Enter an example in the adjacent column and use Flash Fill. Names with middle names, suffixes, or different ordering may need separate handling and careful review.
Is Flash Fill available in Excel for the web?
Flash Fill is a desktop Excel feature, so it may not be available as a command in Excel for the web. Open the workbook in desktop Excel or use a formula or another supported method to transform the data.
Related
- ISERROR in Excel: How It Works and When to Use It
- ISBLANK in Excel: Examples and How It Works
- ISNUMBER in Excel: Check Whether a Cell Holds a Number
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Hide Column Headers in Power BI Table VisualsTo hide every column heading in a Power BI table, select the table visual, open Format visual >…
- Can You Alphabetize in Google Sheets? Sorting Rows and ColumnsYes. In Google Sheets, select the data you want to organize and use Data > Sort range to…
- Excel Data Validation Button: Where to Find and Use ItIn Excel for Microsoft 365 on Windows, find the Data Validation button on the Data tab, in the…
- Create an Outlook Email Template: Step-by-StepTo create a reusable email template in classic Outlook for Windows, compose a message, then choose File >…
- How to Insert a Clickable Table of Contents in WordTo insert a clickable table of contents in Word, apply built-in Heading styles to your section titles, then…
- Data Excel Skills: What They Are and How to Build ThemData Excel skills are the abilities to organize, clean, calculate, analyze, and present information in Microsoft Excel. The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.