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

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?

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 a particular data from adjacent cells and do same for the rest of the columns. Excel now has a shortcut to do the same.

We will learn more ways to use Flash fill at the end of this tutorial!

how to use flash fill in excel

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

  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.

Congratulations!

We’ve learned how to use flash fill.

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018