In Excel for Microsoft 365 on Windows, Flash Fill recognizes a pattern from examples you type and fills the remaining cells; select the first result cell, enter an example, then press Ctrl+E or choose Data > Flash Fill. A Flash Fill pattern is the transformation Excel infers from your sample entries, such as extracting first names or changing a phone-number format.
What an Excel Flash Fill pattern means
Flash Fill is a data-entry feature, not a worksheet formula. You show Excel what you want by typing one or more results beside the original data. Excel examines those examples and tries to apply the same text transformation to nearby rows. For instance, if column A contains full names and you type the first name from the first row into B2, Excel may recognize that you want the first name from each full name in column A.
The pattern is not a formal rule that you configure. It is Excel’s best interpretation of the examples and the surrounding data. A simple transformation may be clear from one example; a less obvious one may need two or more examples. Flash Fill can help combine, split, rearrange, or reformat text, but it does not create a reusable calculation that recalculates when the source changes.
Typical uses include separating first and last names, extracting initials, joining a city and state, standardizing punctuation, or turning differently formatted phone numbers into a consistent display. It is most useful when the desired result is recognizable from examples and you need a quick set of values rather than a formula-driven result.
How to use Flash Fill in Excel
Put the source data in one column and leave an adjacent column available for the results. Type the intended result beside the first source value. If Excel shows a preview of proposed results in the remaining rows, review it; press Enter to accept the preview. If no preview appears, select the first blank cell below your example or the cells you want to fill, then use Ctrl+E or Data > Flash Fill.
- Check that the source rows are arranged consistently and that there are no blank rows interrupting the data.
- Enter one correct example in the output column, aligned with its source row.
- Review any suggested results before accepting them. If the suggestion is wrong or absent, add another example that clarifies the desired pattern.
- Run Flash Fill with Ctrl+E or Data > Flash Fill, then inspect the filled values before using them elsewhere.
On Excel for Mac, use Data > Flash Fill when the command is available in your version. Keyboard shortcuts can differ by keyboard and version, so the ribbon command is the dependable route if a shortcut does not work. The Windows instructions above apply to Excel for Microsoft 365 for Windows.
Concrete examples of useful patterns
Suppose A2 contains Maya Chen and A3 contains Luis Ortega. Type Maya in B2 and use Flash Fill. Excel may return Luis in B3. To extract surnames instead, type Chen in C2; the goal must be demonstrated separately because it is a different transformation. Review names with compound surnames, middle names, or suffixes carefully: a pattern that works for ordinary two-part names may make incorrect assumptions about less uniform entries.
For a formatting example, suppose A2 contains 2065550148 and you want the display (206) 555-0148. Type that format in B2 and try Flash Fill. The result may be useful if the source values are consistently stored as text or digits. Check leading zeros and country codes: if Excel has already interpreted a phone number as a number, a leading zero may have been lost before Flash Fill can use it.
Flash Fill can also combine fields. If A2 contains a first name and B2 a surname, you could type Maya Chen in C2 and ask Flash Fill to continue the combined display. It can be helpful for a one-time cleanup, but when source columns are updated later, the filled names in C will not automatically change. Choose a formula instead when the output needs to stay linked to its inputs.
How Excel recognizes the pattern
Excel compares your typed examples with nearby source values and looks for a transformation that seems consistent. That can include which characters to keep, where to split text, the order of pieces, and changes to capitalization or punctuation. The examples provide clues; they do not guarantee Excel has understood your intention. If several transformations would fit the first row, a second example often resolves the ambiguity.
For example, the name Alex Morgan could produce Alex, Morgan, A. Morgan, or Morgan, Alex. One sample does not establish which result you want. Provide another example, ideally one that has a different length or structure, so you can see whether the same rule applies. The more varied the sample rows are, the easier it is to detect an assumption that only happened to fit the first entry.
Flash Fill is designed for recognizable data patterns, not every possible text operation. It may not infer a complicated rule, and it should not be treated as a substitute for a validation step. A preview is a suggestion, not proof that every row follows your intended rule.
Prepare the source data first
Before filling, inspect the source column for inconsistent separators, extra spaces, blank cells, or entries that do not follow the usual structure. A column that mixes names such as Chen, Maya Chen, and Maya L. Chen can lead to inconsistent results because the same apparent pattern does not apply to every row.
Useful preparation steps include:
- Keep one record per row and place the output next to the source data.
- Remove or account for blank rows and headings within the data range.
- Look for exceptions such as middle initials, extra spaces, alternate date formats, or country prefixes.
- Make a copy of the original column before replacing or deleting anything.
Do not assume that a visible value tells the whole story. A cell may contain a number formatted to look like text, or a date stored as a serial value with a display format. If Excel’s interpretation of the source is important, inspect the cell and test a few different rows before accepting a large fill.
Limits and common mistakes
Flash Fill returns fixed results. If the source value changes after the fill, the corresponding output usually stays as it was. That makes Flash Fill suitable for a one-time transformation, but less suitable for a report that must remain current. For a live relationship, use a formula appropriate to the task or another repeatable data-cleaning method.
A common mistake is accepting a plausible-looking preview without checking exceptions. For example, splitting on the first space may work for most full names but fail for entries with a middle name. Another is running Flash Fill in the wrong output column or selecting an unintended range. Confirm that each result aligns with its source row and that the output column contains what you intended.
Flash Fill is also not a general-purpose arithmetic tool. It is meant to infer text patterns, not perform calculations such as adding a fixed amount to each number or calculating a percentage. Use formulas for calculations, because formulas express the operation clearly and update when their inputs change.
Troubleshooting when it does not work
If Excel does not suggest results as you type, you can still try Data > Flash Fill or Ctrl+E on Windows. If the command produces no useful output, make the intended pattern more explicit by adding a second or third correct example. Keep the examples adjacent to the corresponding source rows and ensure the data is arranged in a way Excel can associate with them.
On Windows, automatic suggestions can be controlled through File > Options > Advanced, under Editing options, by using the Automatically Flash Fill checkbox. If suggestions are not appearing, check that setting. The exact options interface can differ on Mac, so use the Data tab command rather than assuming the Windows settings path applies.
When results are partly wrong, undo the operation immediately if appropriate, correct the examples, and run it again. Then inspect every row, especially unusual entries. Flash Fill can be invoked again after changing the samples, but that does not remove the need to verify the results. If the data has many exceptions, inconsistent formats, or a complex rule, a formula, Power Query, or manual review may be more reliable.
Flash Fill compared with formulas and Power Query
Choose Flash Fill for a quick, one-time change when you can clearly demonstrate the desired output and can review the resulting values. Its main advantage is convenience: you do not need to write a formula for a simple text cleanup. Its trade-off is that the result is static and the inferred logic is not presented as an editable calculation.
Choose a formula when the output should change whenever the source changes, or when you need a transparent, repeatable rule. Formulas can be copied down and inspected, but the right formula depends on the task and the structure of the data. Choose Power Query when you need a repeatable import-and-cleanup process for larger or regularly refreshed datasets. It takes more setup than a quick Flash Fill, but the transformation steps can be applied again during a refresh.
These methods can also be combined. Flash Fill may help create a quick draft or reveal the desired output, while a formula or Power Query is used for the durable version. Whatever method you choose, keep the original data until you have confirmed the cleaned results.
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 the keyboard shortcut differs or is unavailable in your setup.
Why is Flash Fill not showing a preview?
Excel may not see a clear pattern from one example, automatic suggestions may be disabled, or the source data may be inconsistent. Add another correct example, check the Windows Automatically Flash Fill option under File > Options > Advanced, or run the command from the Data tab.
Does Flash Fill update when I change the original cell?
No. Flash Fill writes values rather than a live formula, so changing a source cell does not normally recalculate its filled result. Re-run Flash Fill or use a formula if the output must stay linked to the source.
Can Flash Fill split first and last names?
Yes, when the names follow a pattern Excel can recognize. Type the desired part beside the first source name, then run Flash Fill. Check middle names, compound surnames, and other exceptions because a simple split may not handle them correctly.
Can I undo Flash Fill?
Yes. Immediately after running it, use Ctrl+Z on Windows or Command+Z on Mac to undo the fill. As with other editing actions, inspect the results before continuing so that you can correct unwanted values promptly.
Related
- Excel Data Validation: Can a Dropdown Hold Multiple Values?
- Excel Data Validation: How to Circle Invalid Data Cells
- Excel Data Validation: Add Criteria and Rules
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Protect Sheet and Workbook in Excel: What’s the Difference?In Excel for Microsoft 365 on Windows, use Review > Protect Sheet to restrict changes to a worksheet,…
- Find Circular References in ExcelTo find a circular reference in Excel for Microsoft 365 on Windows, open Formulas > Error Checking >…
- Excel Dependent Drop-Down Lists: Link One to AnotherYes. In Excel for Microsoft 365 on Windows, make the first drop-down with Data Validation, create a named…
- Excel File Won’t Open in Protected View? FixesIf an Excel file will not open in Protected View, first confirm that the file came from a…
- Insert a Checked Checkbox in Word: 3 Simple MethodsTo insert a checked checkbox in Word, use Insert > Symbol > More Symbols and choose the checked-box…
- Excel Table Slicers: How to Add, Use, and TroubleshootAn Excel table slicer is a clickable filter panel for an Excel table: choose a button such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.