What is Flash Fill? AND HOW TO USE? - KING OF EXCEL

Thursday, April 9, 2020

What is Flash Fill? AND HOW TO USE?

What is Flash Fill? AND HOW TO USE?

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.
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
     Enter. Now you are in the next row in that column. You want the same result to appear in the rest of the columns.
  2. 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
    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.
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

Popular Posts