How to Use Flash Fill in Excel Quickly and Easily
In Excel,
Flash Fill automatically fills data when it detects a pattern you start typing. To use it, enter the desired pattern in a cell next to your data, then press Ctrl + E or go to the Data tab and click Flash Fill. Excel will fill the rest of the column based on your example.Syntax
Flash Fill does not use a formula but works as a feature triggered by a shortcut or menu option.
Ctrl + E: Keyboard shortcut to activate Flash Fill.Data > Flash Fill: Menu option to trigger Flash Fill.- Input pattern: You must type an example of the desired output next to your original data.
Flash Fill then fills the rest of the column by recognizing the pattern you started.
excel
No formula syntax; use Flash Fill feature via keyboard or menu.
Example
This example shows how to extract first names from full names using Flash Fill.
Suppose column A has full names:
- A2: "John Smith"
- A3: "Mary Johnson"
- A4: "David Lee"
In B2, type the first name "John" as an example. Then select B2 and press Ctrl + E. Excel fills B3 and B4 with "Mary" and "David" automatically.
text
A2: John Smith A3: Mary Johnson A4: David Lee B2: John (typed manually) Select B2 and press Ctrl + E to fill B3 and B4.
Output
B2: John
B3: Mary
B4: David
Common Pitfalls
- Not providing a clear pattern: Flash Fill needs a clear example to detect the pattern. If the example is ambiguous, it may fill incorrectly.
- Data inconsistency: If your data varies a lot, Flash Fill might not work well.
- Feature disabled: Flash Fill must be enabled in Excel options (usually on by default).
- Manual edits: Flash Fill does not update automatically if source data changes; you must run it again.
text
Wrong example: A2: John Smith B2: Jo (too short, unclear) Pressing Ctrl + E may fill wrong or incomplete data. Right example: B2: John Then Ctrl + E works correctly.
Quick Reference
| Action | How to Do It |
|---|---|
| Start pattern | Type example data next to original data |
| Activate Flash Fill | Press Ctrl + E or go to Data tab > Flash Fill |
| Check results | Verify filled data matches pattern |
| Repeat if needed | Run Flash Fill again if source data changes |
| Enable feature | Check Excel Options > Advanced > Enable Flash Fill |
Key Takeaways
Flash Fill fills data automatically by recognizing patterns you type.
Start by typing an example next to your data, then press Ctrl + E to fill.
Ensure your example is clear and consistent for best results.
Flash Fill does not update automatically; rerun it if source data changes.
Enable Flash Fill in Excel options if it does not work.