In Excel for Microsoft 365 on Windows, go to File > Options > Advanced and check or clear Automatically Flash Fill to control automatic pattern detection. You can also run Flash Fill yourself from Data > Flash Fill or by pressing Ctrl+E after entering an example.
What Flash Fill Does
Flash Fill recognizes a pattern in the values you enter and fills a neighboring column with matching results. It is useful for reshaping text without writing a formula: splitting full names into first and last names, combining separate fields, changing capitalization, or extracting part of an email address. Excel uses the examples you provide to infer the transformation, then proposes results for the other rows.
For example, suppose column A contains names such as Jordan Lee and Priya Shah. If you type Jordan in B2 and begin entering Priya in B3, Excel may show a preview of first names for the rest of the list. Press Enter to accept it. Flash Fill can also combine information: if first and last names are in separate columns, you can type a complete name in a new column and ask Excel to continue that pattern.
Unlike a formula, a Flash Fill result is a set of ordinary values, not a live calculation. If the source name changes later, the filled result does not automatically recalculate. That distinction is important when choosing whether Flash Fill is appropriate for a one-time cleanup or for data that will keep changing.
Where the Flash Fill Setting Is
The Windows setting controls whether Excel tries to recognize a pattern automatically as you type. To find it in Microsoft 365 for Windows:
- Choose File, then Options.
- In the Excel Options window, select Advanced.
- Under Editing options, find Automatically Flash Fill.
- Check the box to allow automatic suggestions, or clear it to stop them, then select OK.
Turning off automatic Flash Fill does not remove the command. You can still request a fill explicitly with Data > Flash Fill or Ctrl+E. This is a useful arrangement if Excel’s automatic suggestions interrupt your typing but you still want the feature available when you need it.
In Excel for Mac, look under Excel > Preferences > Edit for the Automatically Flash Fill option; the wording or placement can vary with the release. The Data tab’s Flash Fill command is another way to invoke the feature. Excel for the web does not provide the same desktop Flash Fill command and options, so use the desktop app or a formula for that workflow.
How to Run Flash Fill Manually
Manual Flash Fill gives you more control because you decide when Excel should infer the pattern. Keep the source information in a nearby column and put the desired output in an empty column. Then:
- Enter the correct result for the first data row.
- Move to the next cell in the output column. If Excel displays a preview of the remaining results, review it.
- Press Enter to accept the preview. If no preview appears, press Ctrl+E on Windows, or choose Data > Flash Fill.
- Check several results, including the first and last rows, before using the output elsewhere.
For instance, if A2 contains sam.chen@example.com and you want the text before the at sign, type sam in B2, then start the next example in B3. A preview may reveal that Excel has understood the pattern. Check names with different lengths or formats; a pattern that looks obvious in two ordinary rows may be ambiguous when the list contains exceptions.
To combine two columns, enter the desired combined value in the first output cell, including any space or punctuation you want between the parts. The examples help Excel infer both the source columns and the formatting. If you want a comma, a middle initial, or a particular capitalization, make that clear in the examples rather than expecting Excel to guess your preference.
Choosing Between Automatic and Manual Flash Fill
Automatic Flash Fill is convenient when you repeatedly make straightforward transformations and want Excel to offer a result while you work. Manual Flash Fill is better when you want a deliberate action, when the worksheet has many nearby text fields that could be mistaken for inputs, or when automatic suggestions distract from data entry.
Neither setting determines whether Excel can understand every pattern. The automatic option controls when Excel attempts to act, not the quality or reliability of the inference. Even with automatic Flash Fill turned on, Excel may not show a preview. You can try the manual command, provide another clear example, or use a formula if the transformation needs to be consistent and repeatable.
- Choose automatic Flash Fill for quick, occasional cleanup where a preview is easy to verify.
- Choose manual Flash Fill when you want to decide exactly when the transformation happens.
- Choose formulas or Power Query when results need to update from changing source data or when the steps must be repeatable.
Why Flash Fill May Not Work
A missing preview does not necessarily mean the setting is off. Flash Fill needs enough information to infer a consistent rule. Try entering a second example, make sure the examples are in the same rows as their source data, and use the command explicitly. If there are blank rows, inconsistent separators, or several possible source columns, Excel may not identify the pattern you intended.
Check that you are using the desktop version of Excel and that the output column is positioned where Excel can recognize its relationship to the source data. Also confirm that the output cells are suitable for filling. Existing content can be overwritten by a manual fill, so use an empty destination column or make a copy first. If the destination contains a formula or values you need, preserve them before proceeding.
Inconsistent source values are another common cause. Consider a list with email addresses that use different conventions, or names that sometimes include a middle name. A couple of examples may suggest one rule even though some rows need different treatment. Add representative examples where possible, inspect the results, and correct exceptions rather than assuming a plausible preview is necessarily accurate.
If Flash Fill still does not infer the transformation, the task may be too complex or ambiguous. For example, extracting a surname from names with different numbers of parts can require a rule about which part counts as the surname. Excel cannot know a business-specific convention from a few examples alone. A formula, a carefully reviewed manual process, or Power Query may be a better fit.
Accuracy, Updates, and Data Handling
Flash Fill is a pattern-recognition aid, not a data-validation system. Always inspect the output before relying on it, especially when the data will be used for customer records, reports, or imports into another system. Check unusual punctuation, accents, blank cells, duplicate values, and rows that do not follow the dominant format. A fast fill can save typing, but it cannot confirm that the interpretation is correct.
Because Flash Fill writes values rather than formulas, later edits to the original cells will not flow through to the result. If an address changes or a source name is corrected, rerun Flash Fill or edit the output yourself. Rerunning it replaces results in the destination range, so check what will be overwritten first. Keep an unchanged copy of important source data when performing a broad cleanup.
Flash Fill works on the patterns in worksheet data; it is not a substitute for a governed data-cleaning process. The setting does not make results dynamic or guarantee that Excel will understand sensitive or irregular records correctly. For a shared workbook or a repeatable process, document the transformation and validate the output rather than relying on an unreviewed suggestion.
Flash Fill Compared with Other Options
Flash Fill is often the quickest option for a one-time transformation, particularly when the pattern is easy to show with examples. Formulas are more suitable when the output must update whenever source cells change. They also make the transformation visible in the formula bar, which can help another person understand how each result was produced. The trade-off is that a formula may take more setup and can be harder to build for irregular text.
Text to Columns is useful when a whole column can be split consistently using a delimiter such as a comma or space. Power Query is useful for repeatable imports and cleanup steps, especially when the same shaping work must be refreshed with new data. These options are not identical to Flash Fill: they suit structured, repeatable transformations better, while Flash Fill is designed for quick pattern-based entry.
Before choosing, ask whether the source data will change, whether every row follows the same rule, and whether you need to repeat the work. For a one-off list with a clear pattern, Flash Fill can be efficient. For live calculations or a recurring workflow, use a formula or Power Query and verify the result with representative rows.
Frequently Asked Questions
How do I turn on Flash Fill in Excel?
In Excel for Windows, go to File > Options > Advanced and select Automatically Flash Fill under Editing options. Select OK to save the change.
What is the shortcut for Flash Fill?
In Excel for Windows, press Ctrl+E to run Flash Fill. You can also choose Data > Flash Fill. On a Mac, use the Data tab command if the shortcut differs in your keyboard layout or Excel release.
Why is Flash Fill not filling automatically?
Automatic Flash Fill may be disabled, Excel may not have enough examples to identify the pattern, or the data layout may be ambiguous. Check the setting, provide another example, and try Data > Flash Fill.
Does Flash Fill update when the original data changes?
No. Flash Fill places fixed values in the output cells, so they do not recalculate when source cells change. Rerun the fill or use formulas for results that need to update.
Can I use Flash Fill in Excel for the web?
The desktop Flash Fill command and its setting are not available in the same way in Excel for the web. Open the workbook in desktop Excel, or use a formula or another supported cleanup method.
Related
- Excel Split Print Area Into Two Pages: Page Breaks
- Excel Split Contents of a Cell Into Two Cells
- Excel Split Multiline Cell Into Rows
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 Hide Rows in Excel: Keyboard Shortcuts and TipsTo hide rows in Excel for Microsoft 365 on Windows, select the row or rows, then press Ctrl+9.…
- Excel Shortcut to Expand a Column: AutoFit and MoreIn Excel for Microsoft 365 on Windows, select a cell in the column and press Alt, H, O,…
- Outlook IMAP Out of Office: Set Up an Automatic ReplyTo set an out-of-office reply for an IMAP account in Outlook, first check whether your email provider offers…
- Google Sheets Unix Timestamp to Date Conversion GuideTo convert a Unix timestamp in Google Sheets, divide seconds by 86,400 or milliseconds by 86,400,000, then add…
- Find Max Value in Excel With Criteria Using MAXIFSTo find the largest value that meets conditions in Excel, use MAXIFS. With sales in D2:D7, salespeople in…
- Edit Data Validation Restrictions in ExcelTo edit a data validation restriction in Excel for Microsoft 365 on Windows, select the cell or range,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.