A column of names lands in your spreadsheet, and you only need one part of each entry. The slow route is clicking into every cell and deleting what you don't want. On twenty rows that's annoying. On a few hundred it eats a chunk of the afternoon, and every manual edit is another chance for a typo.
Excel has a feature that learns the pattern from a single example.
TL;DR: Type the result you want for the first row in the next column, go to the cell below, press Ctrl+E.
Steps
- In the empty column next to your data, type the result you want for the first row. For example, only the name.
- Select the cell directly below it and press Ctrl+E.
- Excel fills the rest of the column using the same rule.
If you prefer the ribbon, the same command is Data > Flash Fill.
Formatting phone numbers works the same way. Type one number with the hyphens added where you want them, move down a cell, press Ctrl+E, and the rest of the list follows.
Checked in Excel for the web and in desktop Excel.
When to reach for it
Flash Fill is at its best on text that follows a visible rule: taking part of a string, adding separators, combining pieces from neighboring columns. It's a cleanup tool for data you received once, not a live calculation.
Common mistakes
- The fill guesses wrong. Type a second example in the next row and press Ctrl+E again. Two examples usually remove the ambiguity.
- Expecting it to update. Flash Fill writes plain values. If the source column changes later, the filled column stays as it was, so run it again or switch to a formula.
- Skipping a spot check. Scroll through the result before deleting the original column. Rows that break the pattern (a missing value, an extra space) are where odd results show up.
Formula and code alternatives
If the source data keeps changing, a formula is the better fit because it recalculates. In Microsoft 365, when the part you need is separated by a space, =TEXTBEFORE(A2," ") returns everything before the first space. If the same file arrives every week, a short pandas script does the job without opening Excel:
df["first_part"] = df["full_name"].str.split(" ").str[0]
For a one-time list, though, Ctrl+E is faster than writing either one.
This post was drafted with AI assistance. The steps come from a walkthrough checked in Excel for the web.
Top comments (0)