Being able to create large data sets that follow a certain pattern in an instant truly is a miracle, that comes in the form of Autofill. Even without any in-depth knowledge of it, most Excel users benefit from it at some point during their tenures. Flash Fill, on the other hand, is an even better variant of the handy Autofill feature that is not quite its alternative, but is actually made to complement it.

You can use the Flash fill to combine names, split names, copy alternating values, and do much more without even typing any formulas. The Flash Fill feature is available in Excel 2013 and later for Windows, and Excel for Mac 2016 and later. Let’s take a closer look at how Excel Flash Fill can save you time and drudgery.

 

How does Excel Flash Fill work?

Mainly there are 3 ways for using the Flash Fill feature:

1. When working with data matching certain patterns, Excel can detect the data structure and suggest to use the Autofill feature.
2. You can select the target range and then click Flash Fill button from the menu.
3. You can drag the Autofill pointer with a right-click, and then select the Flash Fill option.

 

Preview Method

You may have noticed Excel making suggestions to automatically finish your typing. This means that Excel sensed a pattern between your existing data and what you’re typing. If you press the Enter key when you see the values in gray, Excel will populate the cells with the suggested values. In the example below, we have first and last names in column A. When you enter the first name of the first person in the list, Excel will detect this pattern and suggest to complete the rest for you.

If you’re not getting these types of suggestions, the pattern you are trying to implement might be too complicated for Excel.

 

Flash Fill Button

For when Excel can't recognize your pattern, you can access this feature manually. The button is located under the Data Tools section of the DATA tab. Before clicking the Flash Fill button, enter something that follows your pattern and select the range you want to fill.

Empty rows will auto-populate after clicking the button.

 

Autofill Menu

Another way to use Autofill is from the right-click Autofill menu. Dragging the Autofill square with a left-click fills the cells with the default filling method. Dragging the square with a right-click displays the menu containing options for fill methods, as well as the Flash Fill option. Clicking the Flash Fill button will populate the data.

For more information about the Autofill menu and its features see: With Autofill Excel Shows Its Smarts and Saves You Time.

 

Tips & Tricks

Get First Name or Last Name from Full Name

For one of the most common users, let’s revisit our previous example. You can easily split columns using this feature. We begin by typing in the first names and Excel shows its predictions as we are entering the second name.

 

Combine Names

Excel can also detect ‘reverse’ patterns. You can use the Flash Fill feature to merge data from two columns in a third one.

 

Get Initials

You don’t always have to work with your data in its entirety, Flash Fill can also work with initials or indexes. Feel free to combine our tips and experiment! For example, you could use Autofill to create email addresses from initial of the first names and the full last names.

 

Format Phone Numbers

The Flash Fill also works wonders with numbers. For example, you can format numbers with phone number parentheses and dashes easily.

An alternative way to format numbers in phone number style is by using custom number formats. Using number formatting, you can display the values as is, without having Excel convert them into strings. For more information please see: Number Formatting in Excel – All You Need to Know.

 

Limitations

Although the Flash Fill is a smart feature, there might come a time when it won’t see the correct pattern and suggest wrong values. Let's assume you need the first 2 and last 2 numbers in cells that contain large numbers.

Excel shows its predictions as you enter a value in the second cell. Although the first 2 numbers are correct, predicted values suggest copying the values down. For these types of situations, continue entering values manually until you get the correct prediction.

Another important note about the Flash Fill is that it’s not dynamic like formulas. Updates on original values will not affect automatically filled values, so you need to use Flash Fill again.

If Excel can’t see any patterns when trying to use Flash Fill you will see the following error message: