Before we learn how to use flash fill in Excel, there are some questions we need to address first. These questions are: What exactly is Flash fill? Why should we use it? And what can it do for you?
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
What is Flash Fill?
Let’s say there is a pattern evident in the cells. Flash fill can replicate the same across the remaining cells in the column. Flash fill recognizes the pattern and completes it. Thus, making functions like concatenating, left/insert and extract redundant, in a flash!
Think: Sometimes, we wish to do some manipulation. With data in columns, or extract particular data from adjacent cells and do the same for the rest of the columns. Excel now has a shortcut to do the same.
Imagine that you have a table of the first names and last names of people. And you want to get them together in a particular format.
How to Use Flash Fill
15 Excel Data Cleaning Tricks Guidebook
Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages)
1. Create a table same as like picture above and type the data that needs to be extracted out of it in cell C2:
This table is showing first names and last names of six individuals. We need to derive a title name for them. Let’s use the first alphabet of the first name joint with the second name in column D for each individual.
2. Press Enter. Now you are in the next row in that column. You want the same result to appear in the rest of the columns.
3. Now press Ctrl + E on your keyboard and watch the magic happen!
Excel evaluates the pattern applied in the previous cell. It then replicates the pattern in the entire column.
4. Click on the mini toolbox that appears next to the pasted cells. Then click Accept Suggestions.
No more spending hours trying to extrapolate data. By using left or other complicated functions. Do data manipulation with ease!
Tip: You can do the same function manually.
Flash Fill is normally activated by default in Excel. If not; you can do so in the below manner:
Click File -> Options.
Check Automatically Flash Fill. Checkbox under Editing Options group inside Advanced tab and click Ok.
Now to execute the function manually:
After Step 2, Start the function manually by clicking Data -> Flash Fill.
When to use Flash Fill in Excel?
Some examples of using Flash Fill are:
- Concatenating first name and last name using flash fill.
- Separating first name and last name.
- Extracting Initials from full names.
- Formatting phone numbers using flash fill.
- Extracting text before symbol @ from email addresses.
- Extracting names from email addresses.
- Cleaning data; removing extra spaces or punctuations from cells.
- Formatting numbers and text; like toggle case or a ‘proper’ format.
- Advanced combinations like data rearrangements of words and addition of punctuation marks.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12