In Excel for Microsoft 365 on Windows, Flash Fill is on the Data tab: choose Data > Flash Fill, or select the destination cell and press Ctrl+E. Enter one example of the result you want first; Excel uses that example to fill the remaining rows when it recognizes the pattern.
What the Flash Fill button does
Flash Fill is an Excel tool for reshaping text and values by example. Instead of writing a formula or applying a fixed text-splitting rule, you show Excel a desired result for one row. It then looks for a similar pattern in the surrounding data and fills the other rows. Common uses include separating first and last names, combining names, extracting a portion of an email address, and making phone numbers look consistent.
For example, suppose column A contains Ada Lovelace and Grace Hopper, and you want first names in column B. Type Ada in B2, select B3, and run Flash Fill. Excel may fill the remaining entries with Grace. The output is not a formula that continuously refers to column A; Flash Fill writes results into cells.
The feature is most useful for a one-time or occasional cleanup where the rows follow a recognizable pattern. It is not a replacement for a formula or a data-import rule when the source will keep changing or the transformation needs to be repeatable and precisely controlled.
Where to find Flash Fill
In the Windows desktop version of Excel, open the Data tab on the ribbon. The Flash Fill command is in the Data Tools area. If the ribbon is simplified or the window is narrow, the command may be grouped or less prominent, but the Data tab remains the place to look. The Windows keyboard shortcut is Ctrl+E.
To use the command manually, keep the original data in one column and prepare an empty neighboring column for the results. Type the desired output for the first row beside its source value. Select the next blank result cell, or the blank result cells below the example, and choose Data > Flash Fill. Check the generated results before using them elsewhere.
Excel can also offer Flash Fill automatically as you type examples. If the suggestion does not appear, you can still run the ribbon command or shortcut. In Windows desktop Excel, the automatic behavior can be checked under File > Options > Advanced, in the Editing options, where Automatically Flash Fill can be enabled.
How to use it with practical examples
Start with clean, representative examples. A single example may be enough for a very simple pattern, but two examples can make your intention clearer when the source has more than one plausible interpretation. Put the results beside the source data rather than over it, so you can compare what Excel produced with the original values.
- Separate names: If A2 contains Mina Patel, type Mina in B2 to extract the first name, or Patel in C2 to extract the last name. Use a second example if names include middle names or inconsistent spacing.
- Combine fields: If first names are in A and surnames are in B, enter a sample full name in C2, such as Mina Patel. Flash Fill can infer that the two source columns should be joined with a space.
- Extract an email detail: For mina.patel@example.com, an example such as mina.patel may show that you want the text before the at sign. If you instead enter example.com, Excel may infer that you want the domain.
- Reformat a phone number: If the source is consistently entered as ten digits, an example such as (212) 555-0148 can demonstrate a display pattern. Review the result carefully, especially if the values include country codes, extensions, or leading zeroes.
After Flash Fill runs, scan the output from top to bottom, including the last rows. Do not assume a plausible-looking first result proves that every row was interpreted correctly. If you need to keep only the generated results, they are already cell values; copy and paste them as values if you are moving them into a location where you want to avoid other copied formatting or content.
How Excel recognizes the pattern
Flash Fill examines the examples you provide and the nearby data to infer a transformation. It is pattern recognition, not a conversation with Excel: it does not understand whether a string is a person’s legal surname, a product code, or a meaningful date. When several transformations could fit the example, the tool may choose a pattern you did not intend.
Consistency matters. If most names are written as first name followed by surname but some rows contain a middle initial, a suffix, or extra spaces, a rule inferred from the first row may not handle every exception. Similarly, an email address with a plus-tag, such as mina+orders@example.com, may not fit the simple examples in the same way as other rows.
Give examples that represent the different cases in your data, then compare the output against the source. For a column with genuinely different formats, it may be safer to split the records into groups, use a formula with explicit rules, or correct unusual rows manually instead of asking Flash Fill to apply one pattern to all of them.
Common problems and how to fix them
- Flash Fill does nothing: Confirm that you entered an example beside the source data and selected a blank cell in the output column. Then use Data > Flash Fill or
Ctrl+E. If automatic suggestions are missing, check the Windows setting under File > Options > Advanced. - The results stop partway down: Look for blank rows, a change in layout, or values that do not match the pattern. Try adding another example near the point where the data changes, or fill a smaller section at a time.
- The result is wrong for some rows: Excel may have inferred an overly broad rule from your sample. Undo the fill if appropriate, provide clearer examples, or correct exceptional rows manually. Always review the full output before relying on it.
- Leading zeroes disappear: Excel may interpret a digit-only result as a number, where leading zeroes are not retained as part of the numeric value. If the result is an identifier rather than a quantity, set the destination cells to Text before entering or filling results, and verify that the displayed values retain the required zeroes.
- The command is unavailable: Check that you are using a desktop Excel version with the feature and that the Data tab is visible. If you are working in a browser or an older installation and cannot find Flash Fill, open the workbook in a supported desktop Excel app or use another method.
Flash Fill versus formulas
The key difference is that Flash Fill produces values based on examples, while a formula records a calculation or transformation. If you change a source name after using Flash Fill, the corresponding filled result generally does not update automatically. You would need to run Flash Fill again or edit the result.
That makes Flash Fill convenient for a quick, reviewed cleanup, such as preparing a one-off list of first names for a mailing. Formulas are usually better when the source data changes regularly, when the result must update automatically, or when someone else needs to see exactly how each output was calculated. A formula also makes the rule explicit, which can be valuable for repeatable workflows.
There is a further distinction between Flash Fill and commands that split data by a fixed delimiter. If every value follows a simple, consistent separator, a text-splitting command or formula can be more predictable. Flash Fill is handy when the desired result combines several edits or formatting choices that are easy to demonstrate but awkward to describe as one delimiter rule.
Using Flash Fill on Mac and in Excel for the web
On a Mac, look for Data > Flash Fill in the Excel ribbon. The Windows shortcut is Ctrl+E; shortcut behavior can vary with the Mac keyboard and Excel version, so the ribbon command is the dependable option if the shortcut does not work. The workbook’s data and the basic example-driven approach are the same.
Excel’s feature availability can differ between the desktop apps and Excel for the web. If Flash Fill is missing in the browser version you are using, open the workbook in the desktop app and use the Data tab there. Avoid assuming that a web workbook has the same ribbon commands or keyboard behavior as desktop Excel.
Frequently Asked Questions
Is there a keyboard shortcut for Flash Fill in Excel?
Yes. In Excel for Windows, select the appropriate destination cell and press Ctrl+E. On a Mac, use Data > Flash Fill if the shortcut does not work in your version or keyboard setup.
Why is Flash Fill not working?
It may not have enough information to identify your intended pattern, or the selected cell may not be in the output area. Enter a clear example next to the source, select the blank cell below it, and run Data > Flash Fill manually.
Does Flash Fill update when I change the original data?
No. Flash Fill writes results into cells rather than creating formulas linked to the source. Rerun it or edit the results after source data changes, or use a formula if automatic updates matter.
Can Flash Fill combine two columns?
Yes. Put an example of the combined result in a new column beside the source columns, then run Flash Fill on the rows below. Check spacing and unusual rows because Excel is inferring the join pattern from your example.
Can Flash Fill split first and last names?
It often can when names follow a consistent format. Names with middle names, compound surnames, titles, or suffixes can make the intended split ambiguous, so inspect the results and use a more explicit method when accuracy is important.
When Flash Fill is a good choice
Use Flash Fill when you can describe the desired transformation more easily with an example than with a formal rule, and when you can inspect the resulting values. It is especially useful for small, one-time formatting tasks in a tidy table. For recurring imports, changing data, or records that must follow strict rules, prefer a formula or a repeatable data-cleaning process. In either case, keep the original column until you have checked the results and confirmed that the transformed data is suitable for its next use.
Related
- Excel Split Contents of a Cell Into Two Cells
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Scatter Plot in Excel With 3 Variables: How to Make OneTo show three variables in an Excel scatter plot, use a Bubble chart: plot the first variable on…
- Excel Subtotal Visible Rows: Sum Filtered Data CorrectlyTo subtotal only the rows visible after filtering in Excel, use the SUBTOTAL function with function number 9…
- Excel Day Counter: Count Days Between DatesWith a start date in A2 and an end date in B2, enter =B2-A2 to count the days…
- Power Query Reference: What It Does and When to Use ItIn Excel Power Query, a reference is a new query built from the result of an existing query.…
- PowerPoint Present With Notes: Presenter View SetupYes. In Microsoft PowerPoint for Microsoft 365, add your script to each slide’s Notes pane, then start the…
- How to Set Out of Office in Outlook for Windows PCTo set an out-of-office reply in Outlook for Windows, open Automatic replies, turn them on, write your message,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.