To use Flash Fill in Excel for Microsoft 365 on Windows, type an example of the result you want in the column beside your source data, then select the next cell and press Ctrl+E, or choose Data > Flash Fill. Excel detects the pattern and fills the remaining cells; you can also turn automatic Flash Fill on or off in Excel’s options.
What Flash Fill does
Flash Fill recognizes a pattern in examples you provide and applies it to nearby data. It is useful for rearranging or reformatting text without writing a formula. For instance, it can separate first and last names, combine name fields, extract a section of an email address, or make phone numbers follow a consistent display style.
Unlike a formula, Flash Fill does not create a continuing calculation. It writes the resulting text or numbers into cells as fixed values. If the source data changes later, the filled results do not automatically recalculate. This makes Flash Fill convenient for a one-time cleanup, but less suitable when the output needs to stay synchronized with changing source cells.
Use Flash Fill step by step
- Arrange the data so the source values are in one or more columns and the output can go in an adjacent empty column. Keep a copy of the original data if you may need to reverse the cleanup later.
- In the first output cell, type the result you want for the first source row. For example, if A2 contains Maria Chen, type Maria in B2 to extract the first name.
- Move to the next output cell, B3 in this example. Press Ctrl+E on Windows, or choose Data > Flash Fill on the ribbon. Excel should complete the pattern for the remaining rows.
- Inspect the results, especially the first several and last several rows. If Excel did not fill the intended range, select the output cells you want it to complete and run Flash Fill again.
- When the results are right, leave them as values or copy and paste them elsewhere. If you want to replace a source column, check the output first, then copy it and use Paste Special > Values before removing the original.
Flash Fill may show a suggested completion as you type a pattern. Press Enter to accept a suggestion when it appears. If no suggestion appears, use Ctrl+E or the ribbon command instead; Excel does not always infer the pattern automatically.
Examples of patterns Flash Fill can recognize
Flash Fill can handle many predictable text transformations. The following examples illustrate the kind of output to enter before running it:
- Separate names: With full names such as Maria Chen and David Patel in column A, enter Maria in B2 and Chen in C2. Fill each output column separately. If names include middle names, suffixes, or inconsistent spacing, review the results because a simple example may not describe every row.
- Combine fields: If first names are in A and last names in B, type a complete name in C2, such as Maria Chen, and run Flash Fill down column C. Enter the exact spacing, punctuation, or separator you want in the example.
- Extract email details: Given maria.chen@example.com in A2, you might enter maria.chen in B2 or example.com in C2. One example may not be enough if addresses use several formats, so provide additional examples or use a more reliable method.
- Standardize display text: You can show a phone number with a consistent arrangement of digits or alter capitalization, provided the underlying entries follow a pattern. Check that Excel has not omitted country codes, extensions, or leading zeros.
The important part is not the specific sample but whether it communicates the rule unambiguously. For example, a single full name does not tell Excel whether you want the first name, the last name, initials, or a rearranged name. More representative examples can help clarify the intended transformation.
Automatic Flash Fill and its settings
Excel can offer Flash Fill suggestions as you type when automatic Flash Fill is enabled. In Excel for Microsoft 365 on Windows, open File > Options > Advanced. Under Editing options, check or clear Automatically Flash Fill, then select OK. With the option enabled, Excel may display a preview when it recognizes a pattern; accept it with Enter if it matches what you want.
The setting controls whether Excel offers automatic completions; it does not prevent you from running the command manually. Ctrl+E and Data > Flash Fill remain useful when you want to trigger it yourself. If suggestions are distracting, turn off the automatic option and use the manual command only when needed.
In Excel for Mac, Flash Fill is available from Data > Flash Fill, and Control+E is the keyboard shortcut in supported versions. Ribbon layouts and keyboard behavior can vary by Excel release and Mac keyboard settings, so use the Data menu if the shortcut does not work. Flash Fill is not available in Excel for the web; open the workbook in the desktop app to use it.
Why Flash Fill may give the wrong result
Flash Fill infers a pattern from examples; it does not understand the meaning of a field or validate the result against your intentions. A pattern that looks clear in the first few rows may fail when the data contains exceptions. Names with multiple parts, email addresses with different structures, extra spaces, inconsistent punctuation, and blank rows are common sources of unexpected output.
- It fills only part of the data: Check whether the output range is adjacent to the source and whether the pattern is consistent. Select the intended output cells and run Data > Flash Fill again.
- It extracts the wrong part: Give examples from different types of rows, not only the first row. If a rule changes according to a condition, Flash Fill may not be the right tool.
- It copies an unwanted space or character: Check your example for leading or trailing spaces, punctuation, and separators. Excel can reproduce details that are easy to overlook.
- It changes no results after the source is edited: That is expected because Flash Fill creates values rather than a live link. Run Flash Fill again after updating the source, or choose a formula or query for repeatable updates.
- The command is unavailable or appears not to work: Confirm you are using the desktop Excel app, with a pattern example in an adjacent column. Excel for the web does not provide the feature.
To undo a Flash Fill run immediately, press Ctrl+Z on Windows or Command+Z on Mac. If you have made other changes since then, undo may remove those changes as well. For a large cleanup, work on a duplicate sheet or copy of the workbook so that the original remains available for comparison.
Flash Fill compared with formulas and other tools
Choose Flash Fill when you need a quick, one-time transformation and can check the results. It is especially handy for a short list or an irregular but recognizable text pattern that would take longer to handle manually. It is less appropriate for a process that must be repeated every week or that must update whenever source values change.
Formulas are a better fit when results should update dynamically. For straightforward tasks, functions such as LEFT, RIGHT, MID, or TEXTBEFORE can produce repeatable results, although choosing the right formula depends on how consistent the source data is. Excel 2021 and Microsoft 365 include TEXTBEFORE; it is not available in every older Excel release. Formulas also make the transformation rule visible to someone reviewing the workbook.
Text to Columns is useful when you want to split a column using a consistent delimiter, such as a comma or space. Find and Replace can make a simple repeated substitution. Power Query is more appropriate for repeatable imports and multi-step cleanup of larger datasets: you can record transformation steps and refresh the query when new source data arrives. These methods require more setup than Flash Fill, but give more control and repeatability.
Review, preserve, and share the results
Before relying on the filled column, compare it with the original data and check rows that do not follow the usual pattern. Pay attention to codes and identifiers: leading zeros can be significant in postal codes, account references, and product numbers. If a result looks numeric, Excel may treat it as a number; confirm that the displayed value still preserves the characters you need.
When the results are final, consider adding a clear heading and keeping the original source column until the cleanup has been verified. If another person needs to understand how the values were produced, document the rule in a note or use a formula or Power Query instead. Flash Fill itself does not leave a formula describing the transformation in each output cell.
Flash Fill works on the workbook data in Excel; it is not an online lookup service or a substitute for checking sensitive information. Follow your organization’s rules for storing and sharing workbooks, and remove source columns only when you are sure they are no longer needed. Saving a copy before a bulk transformation is a simple way to retain a recoverable original.
Frequently Asked Questions
What is the shortcut for Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+E after entering an example. In supported Mac versions, use Control+E; if that shortcut does not respond, choose Data > Flash Fill.
How do I turn Flash Fill on or off?
In Excel for Microsoft 365 on Windows, go to File > Options > Advanced and check or clear Automatically Flash Fill under Editing options. You can still run Flash Fill manually from Data > Flash Fill.
Why is Flash Fill not working?
It may not recognize the pattern, the examples may be ambiguous, or you may be using Excel for the web. Enter a clearer example, select the intended output range, and run Data > Flash Fill in the desktop app.
Does Flash Fill update when the original data changes?
No. Flash Fill inserts fixed results, not formulas. Run it again after a source change, or use a formula or Power Query if the output must refresh automatically.
Can Flash Fill split first and last names?
Yes, if the names follow a recognizable pattern. Enter the first name and last name in separate adjacent output columns, then run Flash Fill for each column. Review rows with middle names, suffixes, or inconsistent formatting.