Flash Fill in Excel recognizes a pattern in your examples and fills the remaining cells with matching results. In Microsoft Excel for Microsoft 365 on Windows, enter an example beside your source data and press Ctrl+E, or select Data > Flash Fill; check the result before using it because Flash Fill creates fixed values, not formulas.
What Flash Fill does
Flash Fill is a data-entry tool that predicts how you want to transform a column by learning from examples you type. It can separate, combine, rearrange, or reformat text and some number-like data. For instance, if a column contains full names and you type a person’s first name in the next column, Excel may infer that you want the first name from every row.
It is useful for one-off cleanup jobs where the transformation is easy to describe with a few examples but tedious to do by hand. Common tasks include splitting first and last names, extracting an email address’s domain, creating initials, combining city and state, or changing a date or phone number’s display as text.
Flash Fill is not a worksheet function. It does not leave a formula behind, and its results do not recalculate when the source changes. Think of it as an assisted way to create a column of values, rather than a permanent rule connecting that column to the source.
How to use Flash Fill
Keep the original data intact and place the examples in a blank column next to it. A clear header can help you remember what the new column contains. For a name split, for example, put full names in column A and the intended first names in column B. The following steps work in desktop Excel for Microsoft 365 on Windows:
- Type the first correct result in the destination column beside the matching source row.
- Start the next result in the row below. If the pattern could be interpreted in more than one way, type a second correct example as well.
- Select the next blank cell in that destination column, then press Ctrl+E. Alternatively, choose Data > Flash Fill on the ribbon.
- Review the filled results against the source data. Correct any exceptions before relying on the column.
Excel may also offer a preview as you type, which you can accept with Enter. If no preview appears, use the ribbon command or shortcut instead of assuming that Excel has understood the pattern. On a Mac, use Data > Flash Fill if the keyboard shortcut differs on your version or keyboard. Excel for the web may expose a different set of commands; if Flash Fill is unavailable, open the workbook in the desktop app.
Examples of patterns it can recognize
Suppose A2 contains “Mina Shah” and A3 contains “Owen Lee.” Typing “Mina” beside the first name and “Owen” beside the second gives Excel stronger evidence that the desired output is the first word of each full name. Flash Fill can then populate the rest of the destination column. If the source includes names with middle names, suffixes, or different word orders, inspect those rows: a pattern that works on simple examples may not match every person’s naming format.
Other practical examples include:
- Combining fields: type a complete label such as “Seattle, WA” from separate city and state columns, then ask Flash Fill to follow the same arrangement.
- Extracting text: type the part of an email address after the @ symbol to create a domain column.
- Reformatting identifiers: enter a phone number in the desired punctuation style, while checking that leading zeros and country codes remain intact.
- Creating initials: type the initials for several names and review people with middle names or multiword surnames.
Examples should represent the rule you actually want, not merely the first few rows that happen to look alike. For example, if you want only the first name, use examples from a person with a middle name as well as a two-part name when possible. If those examples produce inconsistent suggestions, the task may need a more explicit method.
How Flash Fill makes its prediction
Excel uses the examples you enter to infer a transformation and apply it to nearby rows. You do not write a rule or specify delimiters as you would in a formula or a text-import tool. That makes the feature quick to use, but it also means the result depends on whether Excel has inferred your intent correctly.
There is no universal number of examples that guarantees a correct result. A simple, consistent column may be clear after one example; a column with exceptions or several plausible interpretations may require more examples, or may not be a good fit at all. The preview and the completed output are suggestions to verify, not proof that every row follows the intended business rule.
Important limitations and data quality
Flash Fill writes values into cells. If you later change a source name, address, or identifier, the corresponding Flash Fill result generally stays as it was. Run Flash Fill again after editing the source if you need a refreshed output, and check whether doing so will overwrite anything already in the destination range.
It can also make mistakes that look plausible. Differences in spacing, punctuation, capitalization, missing values, duplicate names, or inconsistent formats can change what Excel infers. A result such as a neatly formatted phone number may still be wrong if the source cell was treated as a number and lost a leading zero. For data that must be exact, compare the output with the source and preserve an untouched copy of the original column.
Flash Fill is not a secure way to conceal sensitive information. Extracting or rearranging data does not remove the original values from the workbook, and the new values may reveal the same information in another form. Apply your normal access controls and privacy practices to the entire file.
Flash Fill versus formulas
Use Flash Fill when you want a quick, static result and the pattern is easy to check. Use a formula when you need the output to update automatically as source cells change, or when you need a repeatable rule that can be inspected and copied through a maintained workbook.
For example, with full names in A2:A4, where A2 contains “Mina Shah,” enter this formula in B2 to return “Mina”: =LEFT(A2,FIND(" ",A2)-1). Copy it down to apply the same rule to other rows. This formula assumes that each name has a space separating the first name from the rest; it will not reliably handle every naming convention. Unlike Flash Fill, the formula recalculates if A2 changes.
Formulas can be more transparent and repeatable, but they require you to choose a rule that fits the data and understand how exceptions are handled. Flash Fill is faster for a small cleanup that will not need to update. For a large, recurring import with many different transformations, Power Query may be a better option because its recorded steps can be refreshed when new data arrives.
Fixing common Flash Fill problems
If Ctrl+E does nothing, first select a blank cell in the intended output column and confirm that the column contains one or more examples. You can also use Data > Flash Fill. Check that the source and destination rows line up; an example beside the wrong source row teaches Excel the wrong relationship.
If Excel fills only some rows or produces inconsistent results, look for an exception in the source data. A blank cell, extra space, different separator, or unusual format may be enough to disrupt the inferred pattern. Add a representative example, clear incorrect output cells, and try again. Do not leave a mixture of old and newly generated results without checking which rows changed.
On Windows, Flash Fill can be enabled or disabled in Excel’s advanced options: choose File > Options > Advanced, then look under Editing options for Automatically Flash Fill. This setting controls automatic suggestions while typing; it does not replace the manual Data > Flash Fill command. If the command is unavailable in a particular Excel edition, use the desktop app or choose a formula or another data-cleanup method.
When another tool is a better fit
Choose the tool based on how often the job will recur and how much control the result needs. Flash Fill is convenient for a quick, visible transformation, but it is not a substitute for a well-defined process when data arrives repeatedly or errors would be costly.
- Choose a formula for results that should respond to changes in source cells and for transformations that need an explicit, auditable rule.
- Choose Text to Columns when a column has a consistent delimiter, such as a comma or space, and you want to split it using a specified separator.
- Choose Power Query for repeatable imports and multi-step cleanup that you want to refresh with new data.
- Choose manual editing when there are only a few irregular rows and no safe general pattern.
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 choose Data > Flash Fill. On a Mac, use the ribbon command if the shortcut differs on your setup.
Why is Flash Fill not working?
Make sure you have entered an example in the destination column, that it lines up with the correct source row, and that a blank output cell is selected. Try Data > Flash Fill. If the source has inconsistent formats, add clearer examples or use a formula or other tool.
Does Flash Fill update when the original data changes?
No. Flash Fill places results as values rather than formulas, so later edits to source cells do not automatically change the filled results. Run it again or use formulas if you need the output to update.
Can Flash Fill split first and last names?
Yes, it can often do that when you type examples in separate columns. Review names with middle names, suffixes, compound surnames, or inconsistent spacing, because a simple example may not express the rule you need for every person.
Can I use Flash Fill in Excel for the web?
Availability can depend on the Excel web experience and its current feature set. If you cannot find Flash Fill under Data, open the workbook in desktop Excel, or use a supported formula or another cleanup method.
Related
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
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 Combine Columns in Excel Without Losing DataTo combine two columns in Excel without removing their original contents, put the result in a new, empty…
- Excel Shortcut to Select an Entire Column on WindowsIn Excel for Microsoft 365 on Windows, press Ctrl+Space to select the entire column containing the active cell.…
- How to Add Audio to a PowerPoint PresentationTo add audio in PowerPoint for Microsoft 365 on Windows, open the slide, choose Insert > Audio >…
- How to Create a Timeline in PowerPoint: Step-by-StepTo create a timeline in PowerPoint, choose Insert > SmartArt > Process > Basic Timeline, enter your milestones…
- Can You Merge Cells in Excel? Options and LimitsYes, you can merge cells in Excel for Microsoft 365 on Windows: select the cells, then choose Home…
- Circular References in Excel: How to Find and Trace ThemTo see circular references in Excel for Microsoft 365 on Windows, open the Formulas tab, choose Error Checking…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.