Flash Fill in Excel detects a pattern in examples you type and fills matching values into nearby cells; on Windows, select the first result cell and press Ctrl+E, or choose Data > Flash Fill. It transforms data such as names or codes, but it does not create a numeric or date series like 1, 2, 3.
What Flash Fill Does—and What It Does Not
Flash Fill is a pattern-recognition feature in desktop Excel. You show it the result you want by typing one or more examples beside your original data. Excel looks for a similar transformation in the neighboring rows and proposes corresponding results. Common jobs include separating first and last names, joining text, changing capitalization, extracting part of an email address, or reformatting an identifier.
The word “series” can mean two different things in Excel. A series might be a sequence of values, such as 1, 2, 3 or successive dates. For that, use the fill handle or the Series command. Or it might mean a column of results generated from a repeated pattern, such as turning full names into initials. Flash Fill is useful for the second meaning, not the first.
Flash Fill produces ordinary cell values, not a formula that stays connected to the source. If the source text changes later, the filled result usually will not recalculate. That makes Flash Fill convenient for a one-time cleanup, but a formula or a repeatable data tool is often better for a workbook that will be updated.
How to Use Flash Fill in Excel for Microsoft 365
On Windows, arrange the source data in one column and leave an adjacent column for the results. Type the desired result for the first row, then start the next row if Excel does not suggest a fill automatically. To run it directly, select the first blank result cell or the range where results should go and press Ctrl+E. You can also use Data > Flash Fill on the ribbon.
- Put consistent source values in a column, for example full names in A2:A6.
- In the neighboring column, type the result you want for the first source value, such as the first name from A2.
- Check whether Excel displays a preview of results in the remaining rows. If the preview is right, press Enter to accept it.
- If no preview appears, select the output cells and press Ctrl+E, or choose Data > Flash Fill.
- Review the completed column before using it elsewhere. Correct a wrong example and run Flash Fill again if necessary.
For Excel for Mac, use Data > Flash Fill when the command is available in your desktop version. Keyboard shortcuts can differ with the Mac keyboard and settings, so the ribbon command is the clearest route. Flash Fill is not generally available in Excel for the web; open the workbook in the desktop app if you need this feature.
Examples of Patterns Flash Fill Can Recognize
Suppose column A contains “Jordan Lee,” “Morgan Patel,” and “Riley Chen.” Typing “Jordan” next to the first name may be enough for Excel to infer that you want first names. For less obvious transformations, provide another example before running Flash Fill. If you want last names instead, enter “Lee” and “Patel” in the first two result cells. Examples guide the pattern; they do not guarantee that Excel will interpret every row correctly.
Other practical uses include:
- Combining fields: Turn separate first-name and last-name columns into a display name, or join a city and state with a comma.
- Changing capitalization: Convert entries such as “sam taylor” into “Sam Taylor,” provided the intended capitalization is clear from the examples.
- Extracting text: Pull a product code or email username from longer strings when the source follows a consistent structure.
- Reformatting values: Change a phone number’s displayed punctuation or turn a date-like text string into a preferred text format.
- Creating abbreviations: Make initials from names or shorten repeated labels using a pattern shown in the first few rows.
These are pattern transformations, not universal parsing rules. For instance, a name with a middle name, a two-word surname, or a suffix may not follow the simple pattern in the first rows. Inspect unusual records rather than assuming that a clean-looking preview means every result is correct.
How to Get More Reliable Results
Flash Fill works best when source entries are reasonably consistent and the intended output is unambiguous. If a column mixes formats, first sort or filter the data to identify the exceptions. You can also make the examples more informative: type two or three correct outputs that represent different cases, then invoke Flash Fill. A single example can be too little evidence when several transformations seem plausible.
Keep the output column beside the source column while creating the pattern. Blank rows, unrelated columns, or a selection that excludes the intended output area can make the feature less predictable. If Excel suggests the wrong result, do not accept it just because it filled many rows quickly. Correct the sample values, run Flash Fill again, and check the results against the original records.
It is also sensible to preserve a copy of the original data before a large cleanup. Flash Fill can overwrite selected cells when you run it on an output range, and its results are values rather than a reversible rule. Keeping the source column intact makes it easier to compare, repair, or redo the transformation.
Common Problems and Fixes
- No preview appears: Type a second example, select the intended output range, and use Ctrl+E or Data > Flash Fill. Confirm that the active cell is in the output column next to the source.
- The output is wrong for some rows: Look for exceptions such as extra spaces, missing parts, different separators, or names with more than two words. Correct those entries manually or handle them as a separate group.
- Ctrl+E does something unexpected: Make sure Excel is the active app and that the correct destination cell or range is selected. You can use Data > Flash Fill instead of the shortcut.
- The command is unavailable: Check that you are using a desktop version of Excel that includes Flash Fill. If you are in a browser, open the workbook in desktop Excel.
- Results do not change with the source: That is expected. Flash Fill writes fixed values. Re-run it after the source changes, or choose a formula or another repeatable method.
There is an option to have Excel automatically use Flash Fill when it detects a pattern. In Excel for Windows, check File > Options > Advanced and look under Editing options for Enable AutoComplete for cell values and Automatically Flash Fill. If automatic suggestions are distracting or inconsistent, you can turn off the automatic Flash Fill setting and run the command only when you choose.
Flash Fill Versus Fill Series
Use Flash Fill when the desired output depends on recognizing a text pattern. Use a fill series when values should progress in a predictable order. To create 1, 2, 3, type 1 and 2 in adjacent cells, select both, and drag the fill handle down. Excel can extend the numerical sequence. Dragging from a single cell containing 1 may copy 1 instead, so the two starting values make the intended increment clear.
For a date sequence, enter a starting date and extend it with the fill handle; use the Auto Fill Options button to choose whether to copy cells or fill dates. You can also use Home > Fill > Series to configure a linear or date series. The exact choice depends on whether you want every day, weekdays, months, or another interval. Flash Fill is not the right tool for generating those ordered values.
In short, Flash Fill answers “What result pattern do these examples suggest?” A fill series answers “What sequence should come next?” Choosing the right operation prevents the common mistake of expecting Flash Fill to continue dates, numbers, or other calculated progressions.
When to Choose a Formula or Another Tool
Choose Flash Fill for a quick, one-time conversion when you can verify the output and do not need it to update. Choose a formula when results must respond to edits in the source cells. For example, a formula-based split or join can recalculate as names change, while Flash Fill leaves its earlier output untouched. Formulas also make the transformation visible to someone reviewing the workbook.
For repeated imports or larger data-cleaning jobs, Power Query can provide a more repeatable process: load the source, apply transformation steps, and refresh the query when new data arrives. Text to Columns is another option when you need to split a column using a consistent delimiter. It is less dependent on inferred examples, but it changes the selected data layout, so make a copy first if the original values matter.
There is no single best tool for every workbook. Consider whether the data is consistent, whether the source will change, how many rows need processing, whether you need to repeat the task, and how important it is for another person to audit the steps. For a small, static list, Flash Fill may be the fastest choice. For changing or recurring data, a formula or Power Query is usually easier to maintain.
Frequently Asked Questions
How do I use Flash Fill in Excel?
Type an example of the output beside your source data, then press Ctrl+E in Excel for Windows or choose Data > Flash Fill. Check the preview and review the completed values.
Why is Flash Fill not working?
Excel may not see a clear pattern, the output cell may not be beside the source, or you may be using Excel for the web. Add another example, select the destination range, and try the ribbon command in desktop Excel.
Can Flash Fill create a number or date series?
No. Flash Fill transforms data based on examples. Use the fill handle or Home > Fill > Series to continue numbers or dates.
Does Flash Fill update when the original data changes?
No. Flash Fill creates fixed values, not linked formulas. Run it again after edits or use a formula or Power Query if the results need to update.
Can I use Flash Fill in Excel for Mac?
Use Data > Flash Fill in the desktop Mac app if the command is available in your version. For keyboard shortcuts, the ribbon command avoids differences in Mac keyboard settings.
Related
- Excel Split Cell Into First and Last Name: A Clear Guide
- Excel Split Cell by Space Delimiter: Methods and Tips
- Excel Sheet Protection: How to Protect and Unlock Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Antique Inventory Spreadsheet Example and SetupAn antique inventory spreadsheet can track each item’s identity, provenance, condition, location, cost, asking price, and sale details…
- Google Sheets Add Timestamp When a Cell ChangesYes. To add a fixed timestamp when a cell changes in Google Sheets, use a small Apps Script…
- How to Edit Custom Number Formats in Excel Step by StepTo edit a custom number format in Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1,…
- Professional Budget Spreadsheet: Build a Clear Excel PlanA professional budget spreadsheet is a clearly organized Excel workbook that compares planned income and spending with actual…
- Yearly Slicer in Excel: What It Means and How to Set One UpA yearly slicer in Excel is a slicer that lets people filter a PivotTable or connected report by…
- How to Find Something in Excel: Cells, Sheets, WorkbooksTo find text, a number, or a formula in Excel for Microsoft 365 on Windows, press Ctrl+F, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.