How to Use Flash Fill in Excel
Introduction
You have a column of full names and need just the first names. You could write a LEFT/FIND formula, but there are 200 rows and the formula gets messy when names have different lengths. Flash Fill reads the pattern from one or two examples you type and fills the rest of the column instantly.
The Dataset
Here's a contact list that needs cleaning up:
| A | B | C | |
|---|---|---|---|
| 3 | Full Name | Phone | |
| 4 | Sarah Johnson | sarah.johnson@acme.com | (212) 555-0148 |
| 5 | James Rivera | james.rivera@globex.com | (415) 555-0237 |
| 6 | Maria Chen | maria.chen@acme.com | (312) 555-0891 |
| 7 | David O'Brien | david.obrien@initech.com | (617) 555-0423 |
| 8 | Priya Patel | priya.patel@globex.com | (213) 555-0765 |
The team needs first names in one column, last names in another, company domains extracted from emails, and phone numbers reformatted without parentheses. That's four transformations across 200 rows.
How Flash Fill Works
Type the result you want in the cell next to the first data value, then press Ctrl + E (or go to Data > Flash Fill).
To extract first names, type "Sarah" in D4. Select D5 and press Ctrl + E. Excel reads the pattern and fills D5:D8 with James, Maria, David, and Priya.
| Full Name | First Name |
|---|---|
| Sarah Johnson | Sarah |
| James Rivera | James |
| Maria Chen | Maria |
| David O'Brien | David |
| Priya Patel | Priya |
One example, one keystroke, and the column is done. No formula to write or copy down.
Key takeaway: Flash Fill compares what you typed to the adjacent data, figures out the transformation, and applies it to every row. The more consistent your source data, the better the results.
Splitting Text
Flash Fill handles most text splitting without formulas.
Last names. Type "Johnson" in E4. Select E5 and press Ctrl + E:
| Full Name | Last Name |
|---|---|
| Sarah Johnson | Johnson |
| James Rivera | Rivera |
| Maria Chen | Chen |
| David O'Brien | O'Brien |
| Priya Patel | Patel |
"O'Brien" splits correctly even though it contains an apostrophe. Flash Fill is matching on the space between first and last name, not on character position.
Company domain. Type "acme.com" in F4. Select F5 and press Ctrl + E:
| Domain | |
|---|---|
| sarah.johnson@acme.com | acme.com |
| james.rivera@globex.com | globex.com |
| maria.chen@acme.com | acme.com |
| david.obrien@initech.com | initech.com |
| priya.patel@globex.com | globex.com |
Flash Fill sees that you took everything after the @ sign.
Reformatting Data
Flash Fill also handles reformatting. To change phone numbers from (212) 555-0148 to 212-555-0148, type "212-555-0148" in G4 and press Ctrl + E:
| Phone | Reformatted |
|---|---|
| (212) 555-0148 | 212-555-0148 |
| (415) 555-0237 | 415-555-0237 |
| (312) 555-0891 | 312-555-0891 |
| (617) 555-0423 | 617-555-0423 |
| (213) 555-0765 | 213-555-0765 |
It stripped the parentheses, removed the space after the area code, and kept the dash. A SUBSTITUTE formula would need three nested calls to do the same thing.
Combining text works the same way. To build a label like "Johnson, Sarah" from the Full Name column, type "Johnson, Sarah" in H4 and Flash Fill reverses the order and inserts the comma for every row.
When Flash Fill Misses
Flash Fill isn't always right. It guesses the pattern from a small sample, and sometimes the guess is wrong.
Common problems:
- Inconsistent source data. If most phone numbers use (xxx) xxx-xxxx but a few use xxx.xxx.xxxx, Flash Fill may misread the pattern on the odd ones out.
- Ambiguous patterns. With the name "Mary Ann Johnson," Flash Fill might return "Mary" or "Mary Ann" as the first name depending on how it interprets the spaces.
- Silent errors. Flash Fill doesn't flag mistakes. It fills every cell with its best guess, and wrong values look the same as right ones.
When the results look off, add a second example. Type the correct value in the next cell down and run Flash Fill again. Two examples often resolve the ambiguity that one couldn't.
If Flash Fill still can't get it right, fall back to formulas. A LEFT/FIND combination gives you explicit control over the extraction logic, which matters when the data is messy enough that pattern matching breaks down.
Flash Fill vs Formulas
Flash Fill produces static values. If the source data changes, the Flash Fill output stays the same. You'd have to run it again manually.
Formulas update automatically. If someone edits a name or email in the source column, every formula that references it recalculates.
| Flash Fill | Formulas | |
|---|---|---|
| Speed | Seconds | Minutes |
| Updates with source | No (static values) | Yes |
| Handles edge cases | Sometimes misses | You control the logic |
| Best for | One-time cleanup | Live data that changes |
For a one-time cleanup before an import or a report, Flash Fill is faster. For a template that processes new data every week, formulas are more reliable.
Conclusion
Flash Fill reads a pattern from your examples and applies it down the column. It handles splitting, extracting, combining, and reformatting text without formulas, and it usually takes one or two examples to get it right.
For formula-based text extraction when you need results that update automatically, see our guide on LEFT, RIGHT, and MID. For replacing specific characters or cleaning up whitespace, check out our guide on TRIM, CLEAN, and SUBSTITUTE.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.