To force Flash Fill in Excel for Microsoft 365 on Windows, type one example of the result beside your source data, select the next cell in that column, and press Ctrl+E. You can also choose Data > Flash Fill. Excel uses the example to infer a pattern and fills the remaining rows; the results are values, not formulas.
What Flash Fill Does
Flash Fill recognizes a pattern in the way you rework text or values, then applies that pattern to nearby rows. It is useful for tasks such as extracting first names from full names, combining two columns, changing capitalization, or removing unwanted characters. You show Excel the result you want, rather than writing a formula or specifying a transformation rule.
For example, suppose column A contains full names such as Maya Chen and Omar Patel. If you type Maya in B2, then select B3 and run Flash Fill, Excel may infer that column B should contain first names. If it recognizes the pattern, it fills the rest of the column. You can also type a second example, such as Omar, before running it; that gives Excel more evidence when the pattern is not obvious.
Flash Fill is different from AutoFill. AutoFill extends a known series, copies a formula, or repeats a cell’s content when you drag the fill handle. Flash Fill attempts to infer a transformation from examples, such as turning a full name into a first name. It does not understand the meaning of your data, so its suggestions need checking.
How to Run Flash Fill Manually
Manual Flash Fill is the most dependable way to request the feature when Excel has not filled a pattern automatically. Keep the source data and your examples in a clear, adjacent layout, and make sure the destination cells are empty before you begin.
- Put the original values in a column, for example full names in A2:A20.
- In the neighboring column, type the intended result for the first source row, such as Maya in B2.
- If the pattern might be unclear, type a second correct result in B3.
- Select the next blank cell in the destination column, or select the destination range you want to fill.
- Press
Ctrl+Eon Windows, or use Data > Flash Fill on the ribbon. Review the results before relying on them.
You can also select Data > Flash Fill after selecting the destination cells. The command is on the Data tab, in the Data Tools group in the Windows ribbon. If you prefer the keyboard, start with one example and use Ctrl+E; do not confuse this with dragging the fill handle, which performs a different operation.
In Excel for Mac, use Data > Flash Fill when the keyboard shortcut is unavailable or differs with your keyboard settings. Ribbon layout can vary by Excel release and window size, but the Data tab is the place to look. In Excel for the web, feature availability and ribbon layout can differ from the desktop app; if Flash Fill is not available in your interface, use a formula or open the workbook in desktop Excel.
Patterns Flash Fill Can Recognize
Flash Fill is suited to transformations that can be demonstrated row by row. Here are practical examples:
- Extracting text: Given Maya Chen, enter Maya to extract the first name, or Chen to extract the surname.
- Combining fields: Given a first name in A2 and surname in B2, enter Maya Chen in C2 to suggest a combined name.
- Changing capitalization: Show an example with consistent capitalization, such as converting mAYA cHEN to Maya Chen.
- Reformatting phone numbers: If a source value contains digits in a consistent arrangement, demonstrate the desired punctuation in the first output example.
- Extracting parts of an identifier: If each code follows the same structure, type the portion you need and check that the pattern holds in every row.
These are examples of likely uses, not guarantees. A source column can contain exceptions: a middle name, a two-part surname, an extension, or a code with a different number of characters. Flash Fill may handle familiar patterns well and still produce a wrong answer on an unusual row. Inspect the full output, especially where the input format varies.
How to Make Flash Fill More Reliable
The quality of the examples strongly affects the result. One example may be enough for a simple, repeated structure, but two or more examples can reveal what you intend. For names, use examples from different kinds of rows rather than two names that happen to share the same structure. For codes, include an example with a different prefix or length if those variations are valid in your data.
Before running Flash Fill, check that the source rows are aligned and that the destination column has no unrelated values that could confuse the pattern or be overwritten. A header is fine, but keep examples directly beside the data they represent. If your data has blank rows or mixed formats, consider cleaning or sorting a copy first; a consistent input makes both pattern recognition and review easier.
After filling, compare a sample of outputs with their source rows, then look specifically for exceptions. Filtering or sorting the destination column can help bring unusual results together. For important lists, check every row rather than assuming a plausible-looking result is correct. If you need a repeatable process, retain the original data and use a method that can be reviewed or refreshed.
Why Flash Fill May Not Run
If Ctrl+E does nothing, first confirm that Excel has an example to learn from and that the active cell is in the intended destination column. Check that the source data is nearby, the output cells are available, and you have selected a suitable cell or range. If Excel cannot identify a pattern, type another example that makes the desired transformation clearer, then try the command again.
On Windows, automatic Flash Fill can be disabled. To check it, open File > Options > Advanced, find the Editing options, and confirm that Automatically Flash Fill is enabled. Even with automatic detection enabled, Excel may not suggest a result for every pattern; using Data > Flash Fill or the shortcut requests it directly.
If the Flash Fill command is unavailable, check that you are using a version and interface that includes the feature, and try opening the workbook in the desktop app. Also check whether a protected sheet or workbook state is preventing edits to the destination cells. If the command runs but produces an unexpected result, undo it immediately with Ctrl+Z on Windows, revise the examples, and try again. On Mac, use the Undo command or the applicable keyboard shortcut for your setup.
Important Limits: Flash Fill Is Not a Formula
Flash Fill writes ordinary results into cells. Those results do not stay linked to the source values. If you change Maya Chen in A2 after creating Maya in B2, the value in B2 does not automatically change. Run Flash Fill again when appropriate, or choose a formula or data-transformation tool if the output must stay synchronized with changing inputs.
Flash Fill also does not provide a formal rule that you can inspect, audit, or apply as a documented transformation. It infers a pattern from examples, and similar-looking inputs can require different interpretations. For example, a family name might contain multiple words, and a simple example showing one-word surnames may not express how every row should be handled. That makes validation essential when the results affect customer records, reports, or other important work.
Keep a copy of the source column before replacing or deleting anything. Flash Fill usually fills a separate destination column, which makes comparison straightforward, but pasted or rearranged results can still make it harder to trace an error back to its original value.
Flash Fill, Formulas, and Other Alternatives
Choose the method that matches how stable and repeatable the task needs to be. Flash Fill is convenient for a one-time cleanup or a quick transformation that is easy to inspect. Other tools may be better when the data changes, the rule is complex, or you need to repeat the same work later.
- Use Flash Fill for a quick, example-driven transformation when the source patterns are consistent and you can check the output.
- Use a formula when results should update as source cells change, or when the transformation must be expressed as a clear rule. Formulas are better for ongoing sheets, though they may take more setup.
- Use Text to Columns when you want to split a column using a known delimiter or fixed-width layout. In desktop Excel, look for Data > Text to Columns; unlike Flash Fill, you specify how to split the data.
- Use Power Query for repeatable imports and multi-step cleanup that you may need to refresh. It requires more initial setup, but keeps transformation steps available to review and reuse.
For example, a one-time list of names might be easy to split with Flash Fill, provided you check compound names. A monthly file delivered in the same format is a stronger candidate for a formula or Power Query, because you can apply a consistent process to new data rather than relying on another inference each time.
Frequently Asked Questions
What is the shortcut to force Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, select the destination cell or range and press Ctrl+E. You can also choose Data > Flash Fill. On Mac, use the Data tab command if the shortcut is unclear for your keyboard setup.
Can I make Excel Flash Fill automatically?
On Windows, check File > Options > Advanced and enable Automatically Flash Fill in the Editing options. Automatic detection is not guaranteed for every pattern, so use the Data tab command or shortcut when Excel does not make a suggestion.
Why does Flash Fill only fill some rows?
The pattern may be ambiguous, or some rows may differ from the examples. Add another representative example, check for inconsistent source values or blank rows, and run Flash Fill again. Review exceptions rather than assuming the filled rows are all correct.
Does Flash Fill update when the original data changes?
No. Flash Fill creates values, not formulas, so editing the source does not recalculate the output. Run Flash Fill again or use a formula or Power Query when you need a process that can be updated with changing data.
Can Flash Fill combine two columns?
Yes. Type a combined example beside the source columns, such as a first name followed by a surname, and run Flash Fill in the output column. Check spacing, punctuation, and rows with missing or additional name parts.
Related
- Excel: Consolidate Text Into One Cell
- Excel Consolidate Tool: How to Combine Data
- Excel Consolidate Tabs: Methods for Combining Worksheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel #SPILL! Error: How to Find and Fix ItTo get rid of a #SPILL! error in Excel for Microsoft 365 on Windows, select the error cell,…
- Excel Sum Not Adding Correctly? Diagnose and Fix ItIf an Excel sum is wrong, first check that the formula covers the intended cells, the entries are…
- Excel Formula Not Working? Find and Fix Common ErrorsIf an Excel formula is not working, check that the cell contains a formula rather than text, confirm…
- Google Sheets: How to Wrap Text in Cells and Fix RowsTo wrap text in Google Sheets in a web browser, select the cell or cells, then choose Format…
- Excel Text Going Out of Cell? Fix Overflow and WrappingIn Excel, text usually spills into the cell to its right when that cell is empty; if the…
- Excel VLOOKUP Not Working With Text? Fix the MatchIf VLOOKUP is not finding text in Excel, first use an exact match and check that the lookup…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.