Excel 2016’s handy Flash Fill feature gives you the ability to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes. The series of entries appear in the new column, literally in a flash (thus, the. Let's say column A contains first names, column B has last names, and you want to fill column C with first and last names combined. If you establish a pattern by typing the full name in column C, Excel's Flash Fill feature will fill in the rest for you based on the pattern you provide. Enter the full name in cell C2, and press ENTER.

If AutoFill isn’t familiar to you, you could benefit greatly from our free Basic Skills E-book, which will show you the basics of Excel formulas, functions and AutoFill.

If you use AutoFill often, you’re probably used to double-clicking on the AutoFill handle to fill down a whole column of data, but you might wonder if you could do this more quickly by using a keyboard shortcut.

There isn’t a specific keyboard shortcut for AutoFilling in this way, but there are some alternatives that could be useful.

Flash Fill was first introduced in Excel 2013. It’s a form of AutoFill that tries to intelligently figure out what kind of data you are trying to fill and automatically provide the values you need.

It’s important to note that Flash Fill does not work with formulas; it will only work with ordinary values.

You can Flash Fill a whole column of data by simply pressing the <Ctrl>+<E> shortcut keys.

Flash Fill is explained in depth in the free Basic Skills E-book.

There actually is a shortcut key for the Fill Down feature: <Ctrl>+<D>

Unfortunately this doesn’t behave in the same way as double-clicking the AutoFill handle. Nothing will happen if you press <Ctrl>+<D> with only a single cell selected. For this to work you need to first select all of the cells that you need to fill down to, which usually takes longer than using the AutoFill handle.

Although there isn’t a shortcut key or Ribbon command that does the same thing as double-clicking the AutoFill handle, Excel still recognizes it as a command. This means that you can use Excel’s Repeat feature to AutoFill as many times as you need after you have done the first AutoFill manually.

The shortcut key for the Repeat command is <Ctrl>+<Y>, but you can also add this command to your Ribbon or Quick Access Toolbar.

Customizing the Ribbon and Quick Access toolbar is covered in our Essential Skills Books and E-books.

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 particular data from adjacent cells and do the 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!

Flash Fill Excel Keyboard Shortcut

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.
   ADVANCED EXCEL NINJA COURSE
   • 200+ Video Tutorials • 80 Case Studies • 3 eBooks • 2 Webinar • 24*7 Dedicated Support
   
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.
   ADVANCED EXCEL NINJA COURSE
   • Course Certificate • Lifetime Access & Free Updates • Lifetime Support • Career Focused
   
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:

Download Excel For Mac With Flash Fillable

• 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.

Flash Fill Excel For Mac