Flash Fill watches what you type in one cell, guesses the pattern and fills the rest of the column for you. For one-time data cleaning it is often faster than writing a formula. It is available in Excel 2013 and newer.
How to use it
- Next to your data, type the result you want for the first row.
- Start typing the second row. Excel shows a grey preview of the rest.
- Press Enter to accept, or press Ctrl + E at any time to fill the column.
Example: first name from full name
Column A has "Priya Sharma". In B2 type Priya. Press Ctrl + E in B3, and every row shows the first name.
Example: build an email
In C2 type priya.sharma@company.com once, from the name in A2. Ctrl + E creates the email for every other name.
More things it can do
- Change case, for example make names Proper Case.
- Extract digits from text such as an invoice code.
- Add a prefix or format phone numbers like 98765-43210.
Flash Fill is not a formula
The result is fixed text. If the source data changes later, the Flash Fill cells do not update. When the data keeps changing, use formulas such as LEFT, MID, TEXTBEFORE or TEXTJOIN.
If it does not work
- Give it a second example. One example is sometimes not enough to see a pattern.
- Make sure Flash Fill is turned on in File, Options, Advanced, under Automatically Flash Fill.
- Keep the helper column right next to the source data.