In Excel for Microsoft 365 on Windows, enter an example of the result you want beside your source data, then select the next cell down and press Ctrl+E to run Flash Fill. You can also choose Data > Flash Fill; review the results before using them, because Flash Fill creates values rather than a formula that updates with the source.
What Excel Flash Fill does
Flash Fill recognizes a pattern in examples you type and applies that pattern to the rest of a nearby data range. It is useful for simple text transformations: splitting a full name into first and last names, joining pieces of text, changing capitalization, or extracting a consistent part of an entry. You provide the desired output; Excel infers how to reproduce it.
For example, suppose column A contains names such as Maya Chen and Jordan Lee. If you type Maya in B2 and Chen in C2, then start entering Jordan and Lee in the next row, Excel may suggest the remaining first and last names. Alternatively, type the first two first names in column B, select the cells below them, and use Flash Fill. The exact recognition depends on how consistent the source values are and how clearly the examples show the intended pattern.
Flash Fill is not a calculation function. The results are ordinary cell values, not formulas linked to the original entries. If a source name changes later, the filled result does not automatically change with it. That makes Flash Fill convenient for a one-time cleanup, but it is not always the right choice for data that will keep changing.
Step-by-step instructions for Windows
- Put the source data in a column and leave an adjacent column available for the results. A header such as Full Name in A1 and First Name in B1 can help you keep the fields clear.
- In the first result cell, type the output you want for the first source row. If A2 contains Maya Chen, type Maya in B2.
- Move to the next result cell and begin typing the next example. For instance, if A3 contains Jordan Lee, type Jordan in B3. Excel may display a preview of the remaining values in the column.
- If the preview matches your intent, press Enter to accept it. If Excel does not show a preview, select the blank result cells where you want the output and press Ctrl+E, or choose Data > Flash Fill.
- Inspect the completed column for incorrect or blank results before copying, sorting, or sharing the data.
You can also provide more than one example before invoking Flash Fill. More examples are particularly helpful when a pattern could be interpreted in different ways. To split a name, for instance, one example may not tell Excel whether you want the first word, the last word, or a middle name. Show the exact result you want, then use Data > Flash Fill or Ctrl+E on the range below it.
Useful examples and ways to apply it
The same feature can handle a range of everyday cleanup tasks. Keep the destination next to the source where practical, and demonstrate the format you want rather than expecting Excel to guess what a field means.
- Separate names: Given entries such as Maya Chen, type Maya in a First Name column and Chen in a Last Name column. Flash Fill can infer each output column separately.
- Combine fields: If separate columns contain a first name and last name, type a complete name in a new column, including the space and punctuation you want. Flash Fill can repeat that arrangement for similar rows.
- Extract an identifier: If product codes consistently begin with a short prefix, type the prefix from a sample code. Check every result, especially if some codes have different lengths or formats.
- Standardize capitalization: Type an example in the capitalization you need, such as a properly capitalized name. Flash Fill may apply that style to comparable entries.
- Reformat contact details: If phone numbers have consistent digit patterns, enter one example in the preferred display style. Verify that the output has not lost digits or changed meaningful leading zeros.
- Build a label: Type an example that combines a city and region, or a category and code, with the separator you want. Flash Fill can reproduce the visible pattern when the source fields are consistent.
These are pattern-based transformations, not semantic interpretation. If one row contains a suffix, a middle initial, an extra space, or a differently formatted code, Excel may produce a result that looks plausible but is wrong. Treat the preview as a proposal, not as a guarantee.
How to give Flash Fill a reliable pattern
Flash Fill works best when source values follow a consistent structure and the examples make one interpretation obvious. Before using it, look down the source column for exceptions: blank cells, extra separators, inconsistent spacing, titles, suffixes, or values entered in different formats. A tidy sample range gives Excel a better chance of recognizing the intended transformation.
- Start with a representative example, not an unusual row. If the first entry has a middle name or a company suffix, use a simpler row first when one is available.
- Enter a second example when the desired output is ambiguous. For a list of email addresses, for example, two examples can make clear whether the output should be the part before the @ sign or a different component.
- Use a separate destination column for each distinct result. For names, keep first names and last names in separate columns rather than trying to produce several unrelated outputs at once.
- Make the output format explicit. Include spaces, hyphens, punctuation, or capitalization exactly as you want them to appear.
- Check the top, middle, and bottom of the completed range, and pay particular attention to rows that differ from the majority.
If a preview is wrong, do not accept it just because most rows look right. Add or correct examples to clarify the pattern, then run Flash Fill again on the intended range. For a one-off cleanup, you can also edit individual results manually. If there are many exceptions or the transformation must be repeatable, a formula or a data-cleaning workflow may be a better fit.
Limits and common problems
A frequent misunderstanding is that Flash Fill creates a live connection between the original and transformed data. It does not: changing a source cell later will not reliably update its corresponding Flash Fill result. If the source list changes regularly, you may need to run Flash Fill again or choose a formula-based method that recalculates.
Other problems usually come from inconsistent input or from trying to apply a pattern that is not clear from the examples. Watch for these situations:
- No preview appears: Excel may not have recognized the pattern yet. Type another example, select the cells to fill, and use Data > Flash Fill or Ctrl+E.
- Some rows are wrong: The source may contain exceptions, or the examples may fit more than one possible rule. Correct the examples and inspect the output before relying on it.
- Blank or unexpected output: Empty source cells, extra spaces, or inconsistent separators can interfere with the inferred pattern. Review the affected source rows rather than assuming every result is a software error.
- Leading zeros disappear: Excel may treat a digit sequence as a number in some workflows. Check the displayed result and the underlying cell values, especially for postal codes, account identifiers, and phone numbers that should be treated as text.
- Results stop partway down: Confirm that the selected destination range covers the rows you intend to fill and that there is not a gap or a different data pattern separating the rows.
Flash Fill can be toggled in Excel’s options on Windows under File > Options > Advanced, in the Editing options area, using Automatically Flash Fill. If automatic suggestions are disabled, the Data tab command can still be used when available. Turning off automatic suggestions changes how Excel offers the feature; it does not turn the results into formulas.
Flash Fill, formulas, and other choices
Choose Flash Fill when you need a quick, visible transformation of a reasonably consistent list and are comfortable checking static results. It is easy to use without writing a formula, and it lets you specify an output by example. It is less suitable when the source changes often, when exact repeatability matters, or when every possible exception must be handled by a defined rule.
Formulas are often preferable for a live worksheet because they can update when source cells change. They also make the transformation rule visible in the formula bar, which can help with auditing and reuse. Their trade-off is that you need to select an appropriate formula and account for the Excel versions used by everyone opening the workbook. For complex recurring cleanup across multiple files, Power Query may be worth considering; it is designed for importing and transforming data through recorded steps, rather than inferring a result from a couple of typed examples.
There is no need to choose only one approach for an entire workbook. You might use Flash Fill to prepare a one-time list, formulas for fields that must stay connected to source values, and Power Query for a repeated import process. Keep the original data until you have checked the transformed output, particularly before deleting columns or distributing the workbook.
Availability on Mac and in Excel for the web
Flash Fill is available in desktop Excel on Windows and Mac, though the ribbon layout and keyboard behavior can vary by version and keyboard settings. On a Mac, look for Data > Flash Fill in the ribbon. If a keyboard shortcut is not recognized, use the ribbon command rather than assuming the Windows shortcut behaves identically. The feature’s purpose is the same: provide examples, generate values, and check the results.
Excel for the web may not expose the same Flash Fill command or behavior as the desktop application. If you cannot find it in the browser version, open the workbook in desktop Excel and use the Data tab. Availability can change as Microsoft updates the web app, so checking the current ribbon is more reliable than relying on an old shortcut tip.
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 and selecting or moving to the cells to fill. You can also use Data > Flash Fill. On Mac, use the ribbon command if the shortcut differs on your setup.
Why is Flash Fill not working?
Excel may not recognize the pattern, automatic suggestions may be disabled, or the source data may be inconsistent. Add another clear example, select the destination cells, and run Data > Flash Fill. Check that the command is available in your version of Excel.
Does Flash Fill update when I change the original data?
No. Flash Fill produces values, not linked formulas. If source entries change, rerun Flash Fill or use a formula-based solution when the output needs to recalculate automatically.
Can Flash Fill split first and last names?
Yes, when the names follow a sufficiently consistent pattern. Type a first-name example in one destination column and a last-name example in another, then use Flash Fill in each column. Review names with middle names, titles, or suffixes carefully.
Can I undo Flash Fill?
Immediately after filling, use Ctrl+Z on Windows or Command+Z on Mac to undo the action. You can also edit or clear the resulting cells. If you have made later changes, review them before undoing, since Undo reverses the most recent workbook actions first.
Related
- Excel Data Validation Based on Another Cell: How It Works
- Excel Data Validation: Make Lists Dynamic
- Excel Data Validation Date Picker: Limits and Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Roundup Excel Without Decimals: 10 Reliable MethodsTo round a value in Excel to the nearest whole number, use =ROUND(A2,0); to make every positive value…
- Excel Conditional Formatting for Yes/No Red and GreenYes—Excel can automatically color “Yes” green and “No” red with conditional formatting. For a status list in B2:B100,…
- Can You Sort by Date in Google Sheets? Steps and TipsYes. In Google Sheets in a web browser, select your table and choose Data > Sort range >…
- Excel Not Responding and Unsaved? Recover Your WorkIf Excel is not responding and your work is unsaved, wait briefly and avoid repeatedly clicking; the app…
- Google Sheets Duplicate Data: Find and Remove ItYes. Google Sheets can find, highlight, and remove duplicate data: use Data > Data cleanup > Remove duplicates…
- Excel Day Care in Jurong East: Location, Fees and ChecksIf you’re looking for Excel Day Care in Jurong East, confirm that the specific centre is currently licensed…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.