To use Flash Fill in Excel for Microsoft 365 on Windows, type the result you want beside your source data, then press Ctrl+E; Excel detects the pattern and fills the remaining rows. You can also choose Data > Flash Fill. It is useful for splitting, combining, or reformatting text quickly, but its results are fixed values rather than formulas that update when the source changes.
What Flash Fill does
Flash Fill recognizes a pattern in examples you provide and applies it to nearby data. For instance, if a column contains full names and you type the first person’s given name in the next column, Excel may infer that you want the given name from each remaining full name. It can also combine text from separate columns, rearrange pieces of text, or apply a consistent format to entries such as phone numbers.
The feature was introduced in Excel 2013 and is included in current desktop versions of Excel. It is an automation aid, not a worksheet function: Flash Fill does not put a formula in each result cell. That difference matters if the source data will change later. Flash-filled results do not recalculate when you edit the original entries.
Flash Fill works best when the source values have a consistent structure and your example makes the intended transformation clear. It does not understand your business rules or verify that a result is correct. Treat the output as a suggested pattern, particularly when the data is inconsistent or the transformation affects important records.
How to use Flash Fill step by step
Put the source data in one column and leave an adjacent column empty for the results. Using a separate output column makes the result easier to inspect and helps protect the original information. For example, if full names are in A2:A6, put your intended first-name results in column B.
- Enter a heading such as First name in B1, if you are using headings.
- In B2, type the result you want for the first source entry. If A2 contains Maria Santos, type Maria in B2.
- Move to B3, the next output cell. Depending on your Excel settings and the clarity of the pattern, Excel may show a preview of the remaining results as you type.
- If the preview is correct, press Enter to accept it. If no preview appears, press Ctrl+E on Windows, or choose Data > Flash Fill.
- Check several results, including entries near the bottom of the list, before using or sharing the output.
You can also start with one example and invoke Flash Fill directly from the next output cell. If the pattern is ambiguous, provide a second example before running it. Two examples can help Excel distinguish between similar interpretations, such as extracting a first name rather than the first word of a company name.
On Excel for Mac, use Data > Flash Fill if the keyboard shortcut is unavailable or behaves differently on your setup. The shortcut is Control+E in supported Excel for Mac versions; it is not Command+E. On Excel for the web, feature availability can differ from desktop Excel, so use the desktop app if Flash Fill is not offered in the ribbon.
Practical examples of patterns
Flash Fill is especially handy for small, one-time cleanup jobs where the desired result is easy to demonstrate. Here are common examples of the kind of transformation it may infer:
- Extracting a name part: From Maria Santos, type Maria to extract the first name, or Santos to extract the surname. Names with middle names, suffixes, or different ordering need extra checking.
- Combining columns: If A2 contains Maria and B2 contains Santos, type Maria Santos in C2. Excel may reproduce the combination down the column. You can include a comma or other separator in your example if that is what you want.
- Reformatting phone numbers: If a source value is 4155550136, an example such as (415) 555-0136 may teach Excel the display pattern. Check that the source is stored in a way that preserves every digit, especially any leading zero.
- Creating email-style text: If you have first and last names in separate columns, an example such as maria.santos can show Excel how to combine them. This does not confirm that the resulting address exists or is available.
- Rearranging text: If entries follow a predictable structure, an example can show that you want to change the order or punctuation of the pieces. Check cases that contain extra spaces or additional words.
These examples describe patterns, not guarantees. A name such as Ana Maria Costa can be interpreted in more than one reasonable way. If you only provide Ana as an example, Flash Fill may apply a rule that is inappropriate for other rows. Inspect exceptions rather than assuming every row follows the first one.
What affects Flash Fill accuracy
Flash Fill infers a transformation from the examples and nearby data. It does not ask you to define a formal rule, so a clear and representative example is important. Use examples that reflect the actual variety in the list: include a value with a middle name, a different number of digits, or an unusual separator if those cases occur.
Data consistency also matters. Extra spaces, abbreviations, missing components, different punctuation, and mixed formats can lead to results that do not match your intent. For example, a column of phone numbers may include country codes in some rows but not others. A pattern based on a local number might then produce a misleading result for international entries.
Excel may also treat entries as numbers, dates, or text differently. A value such as 00127 can lose its leading zeros if it is stored as a number. Dates can be displayed in different ways depending on cell formatting and regional settings. When exact characters matter, inspect the source values and the results rather than judging only by how a cell appears on screen.
A sensible review is to compare several outputs with their source rows, include known exceptions, and check the first and last populated records. If the list is large or the result will be used in reporting, do not rely on a quick visual scan alone. Make sure the transformed values meet the requirements of the task.
Common problems and how to fix them
No preview appears: Confirm that you entered an example in the column next to the source data and that the next output cell is selected. Try entering a second example, then choose Data > Flash Fill or press Ctrl+E on Windows. If automatic previews are turned off, the command can still be used manually.
Flash Fill returns the wrong pattern: Undo the result with Ctrl+Z on Windows, revise the example, and try again. Two or more examples that show the same intended rule can remove ambiguity. If the source column contains several different structures, separate the cases or use a more explicit method instead.
Only some rows fill: Look for blank rows, existing values in the output column, or changes in the source structure. A gap can make the range harder to interpret. Try a clean, adjacent output column with no unrelated entries, and check that the source range is continuous.
The output loses zeros or changes appearance: Check the cell format and the underlying value. Phone numbers, postal codes, and identification codes are often better treated as text because they are labels, not quantities to calculate. If Excel has already converted a value, formatting the cell afterward may not restore digits that were lost from the stored value.
Results do not change after editing the source: That is expected. Flash Fill creates fixed output values. Run it again after changes, or use a formula or another repeatable data-cleaning method when the output must stay linked to its source.
Flash Fill versus formulas and other tools
Choose Flash Fill when you need a quick, one-time transformation and can verify the output. Choose a formula when results should update as source cells change. For example, a formula can join text from two cells, while Flash Fill writes the joined result as a value. Formulas can also be filled down and audited, but they require a clearly defined rule and may need adjustment when the source format varies.
Power Query is often a better choice for repeatable imports or larger cleaning tasks. It provides steps for transforming data and can repeat those steps when you refresh the query. That extra structure takes more setup than typing an example, but it is more suitable when the same cleanup must be performed again on new data.
Text to Columns can be useful when values are consistently separated by a delimiter, such as a comma or space. Unlike Flash Fill, it asks you to specify how to split the data. For more complicated or regularly changing data, a formula or Power Query transformation is usually easier to explain and reproduce.
In short, the choice depends on whether you need speed, a live relationship to the source, or a repeatable process. Flash Fill is convenient, but it is not a replacement for every text-cleaning tool and does not validate the meaning of the result.
Frequently Asked Questions
What is the shortcut for Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+E with the next output cell selected. On supported Mac versions, use Control+E; Data > Flash Fill is an alternative when the shortcut is unavailable.
Why is Flash Fill not working?
Check that the source data is beside an empty output column and that you have entered at least one clear example. Add a second example if the pattern is unclear, then run Data > Flash Fill manually. Gaps or inconsistent source formats may also affect the result.
Does Flash Fill update automatically when I edit the source?
No. Flash Fill writes fixed values, not formulas. If you change the source later, rerun Flash Fill or use a formula or Power Query when results need to update through a defined process.
Can Flash Fill split first and last names?
Yes, if the names follow a pattern that Excel can infer. Type the desired part beside a representative full name, then use Flash Fill. Names with middle names, suffixes, or different ordering may need separate handling and careful review.
Can I undo Flash Fill?
Yes. Immediately after running it, press Ctrl+Z on Windows, or use the Undo command. As with other spreadsheet edits, check the affected cells before continuing so you know whether the output was removed.
A safe workflow for using Flash Fill
Before applying a transformation to a working file, keep the original source column intact and create results in a new column. Use examples that cover the real variation in the data, run Flash Fill, and inspect both normal entries and exceptions. If the output is important, save a copy or keep a clear record of the original values before replacing or deleting anything.
That workflow takes only a little longer than pressing Ctrl+E immediately, but it helps prevent a plausible-looking pattern from becoming an unnoticed error. Flash Fill is most useful when you treat it as a fast assistant: it proposes values from examples, while you remain responsible for confirming that those values are correct.
Related
- Superscript in Excel: Make Numbers Small and Raised
- Excel Random Numbers Between a Range and Value
- Excel Spin Button: Setup, Linking, and Troubleshooting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Not Recognizing Numbers? Fix Text-Formatted CellsExcel usually fails to recognize numbers because the cells contain text that looks numeric, often after a paste…
- VALUE Formula Not Working in Excel? Causes and FixesIf Excel’s VALUE formula is returning an error or leaving text unchanged, check whether the cell contains a…
- Excel Group Rows Not Working? Fix the Common CausesTo group rows in Excel for Microsoft 365 on Windows, select the row numbers, then choose Data >…
- Excel SUMIFS Not Working? Causes and FixesYes—Excel SUMIFS can return zero, the wrong total, or an error when its ranges, criteria, or data types…
- Excel Filter Button Not Working? Fix Common CausesIf the Excel filter button is missing, greyed out, or not filtering the rows you expect, check that…
- Excel VLOOKUP Not Working? Common Causes and FixesExcel VLOOKUP usually fails because the lookup value does not exactly match the first column of the selected…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.