2022 updates MS Excel - Data Cleaning Course Enroll now  
ExcelTutorials

What is Flash Fill ? How to use flash fill in Excel ?

2 Mins read

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

Excel Vlookup Formula

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.

you have a table of the first names and last names of people. And you want to get them together in a particular format

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.

Now you are in the next row in that column

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.

Excel evaluates the pattern applied in the previous cell. It then replicates the pattern in the entire column

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.

Flash Fill in Excel

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!

Advanced Options for working with Excel-Enable AutoComplete for Cell Value

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.

How to use flash fill in Excel-Ctrl E

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

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…