Concept
Available in Excel 2013 and later.
1. How it works
You show Excel the pattern once; it repeats it for the whole column.
| A | B | |
|---|---|---|
| 1 | Full Name | First Name |
| 2 | Rahul Sharma | Rahul |
| 3 | Neha Gupta | |
| 4 | Ravi Kumar Singh |
- Type
Rahulin B2. - Select B3 and press Ctrl + E (or Data → Flash Fill).
- Excel fills Neha, Ravi.
2. Things Flash Fill does well
| Source | You type | Flash Fill gives |
|---|---|---|
| Rahul Sharma | Sharma, Rahul | Gupta, Neha ... |
| rahul sharma | Rahul Sharma | proper case for all |
| 9876543210 | 98765-43210 | same format for all |
| rahul.sharma@gmail.com | rahul.sharma | usernames |
| Rahul Sharma | RS | initials |
| INV-2026-0457 | 0457 | serial numbers |
It can also combine columns: with first name in A and city in B, type Rahul - Delhi in C2 and press Ctrl + E.
3. When it guesses wrong
If the pattern is unclear, Flash Fill may guess wrong — for example, middle names in "Ravi Kumar Singh" when you wanted the last name. Fix it by correcting one wrong cell; Excel re-learns the pattern from your correction. Giving 2–3 varied examples up front helps.
4. The big limitation: it's static
Flash Fill types values, not formulas. If the source data changes, the result does not update. So:
- One-time data cleanup → Flash Fill is perfect.
- A sheet that gets new data every month → use formulas (Lesson 1 and 2).
5. Always check the results
Flash Fill never shows an error; it just fills. Scroll through the output, especially rows that look different from the rest (extra spaces, three-word names, missing values).
6. If Ctrl + E does nothing
Make sure the example column is right next to the data (no empty column between). Check File → Options → Advanced → "Automatically Flash Fill" is on.
Common mistakes
Using Flash Fill on data that will change later. Not checking unusual rows. Leaving a blank column between the source and the example.