Categories
Excel

Flash Fill in Excel: Everything You Need to Know

Have you ever wanted to combine data from multiple cells into a single cell? Or maybe you wished there was a way to extract data from part of a cell and use it elsewhere? And wouldn’t it be great if you could do these things without learning about a lot of complicated functions in Excel?

You can accomplish all of this using Flash Fill!

Flash Fill analyzes the information you enter into cells and automatically fills in data when it detects a pattern. It provides a fast way to combine, extract, or transform data based on examples you provide.

This article will provide you with some guidelines for using Flash Fill, along with several examples, and some suggestions for troubleshooting if you’re not seeing the results you anticipated. Let’s get started!

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

Guidelines

Flash Fill is a pretty easy and intuitive feature in Excel but here are a few things you should keep in mind:

  • This feature only works with columns of data, not rows.
  • Excel only fills in cells below the one(s) where you type your sample data.
  • Example text needs to be entered into cells that are adjacent to the columns of text you’re using as your data source.
  • Results aren’t dynamic – if you change your source data afterward, the results won’t update.

Example: Combining Data

In this example, we’re going to combine each person’s first name and last name into a single a single cell.

A list of first and last names

Step #1 – Select cell C2, type Paul Campbell and press Enter.

The first full name in the list is filled in

Step #2 – In cell C3, type the first character or two for Peggy Cosgrove. Having identified the pattern, Excel displays Peggy Cosgrove and the remaining entries using a light grey colour in column C.

Remaining names are filled in using Flash Fill

Step #3 – Press Enter. Each cell in column C displays a full name.

Complete list of names
Using Flash Fill to Create Full Name

The Flash Fill Options Button

The Flash Fill Options button lets you adjust what happens after a range of cells is updated.

You may have noticed the Flash Fill options button next to Pete Draper’s name in cell C4, after all of the names had been filled in. If you click this button, you’re presented with a list of choices.

Flash Fill Options button
Flash Fill Options Button

The options are:

  • Undo Flash Fill. You can undo changes if you need to.
  • Accept suggestions. Selecting Accept suggestions allows you to keep the results and removes the button.
  • Select all 0 blank cells. This entry is unavailable because there are no blank cells, otherwise they would be selected.
  • Select all 5 changed cells. Selecting the changed cells allows you to efficiently format, copy, or otherwise manipulate them.

Example: Extracting Data

In this example, we’re going to extract each person’s first name from a column showing full names then we’ll display the area code from each person’s phone number.

List of names and phone numbers

Step #1 – Select cell C2, type Paul and press Enter.

First name filled in

Step #2 – In cell C3, type the first character or two for Peggy. Having identified the pattern, Excel displays Peggy and the remaining entries using a light grey colour in column C.

Flash Fill adds remaining first names in the list

Step #3 – Press Enter. Each entry displays a person’s first name from column A.

List of names and phone numbers with the first names filled in
Using Flash Fill to Extract First Name

The same technique would work for area codes. As long as the source data has been entered consistently, you can extract the data you need.

Flash Fill extracts the area code from phone number field
Using Flash Fill to Extract Area Code

Example: Transforming Data

In this example, we’re going to combine each person’s first and last name with a domain name to create e-mail addresses.

List of full names

Step #1 – Select cell B2, type paul.campbell@xlbasics.com and press Enter.

User manually enters the first e-mail address

Step #2 – In cell B3, type the first character or two of peggy. Having identified the pattern, Excel displays peggy.cosgrove@xlbasics.com and the remaining entries using a light grey colour in column B.

Flash Fill generates the remaining e-mail addresses

Step #3 – Press Enter. The entire list of e-mail addresses is displayed as a full name in column B.

List of names and e-mail addresses
Using Flash Fill to Create E-mail Addresses

Troubleshooting

There are a few scenarios that can potentially cause problems with this feature.

Flash Fill is Turned Off

Flash Fill is enabled by default but, if the feature is just not working, take a quick look in the Options dialog box and make sure that the it is turned on.

Go to File > Options > Advanced > Editing options and select the Automatically Flash Fill checkbox.

Excel Options dialog box with Automatically Flash Fill checkbox enabled
Options Dialog Box with Flash Fill Enabled

Excel Can’t Recognize a Pattern

In the example I used for transforming data, I created e-mail addresses by combining each person’s first name, last name, and a domain name. If I had wanted to combine first initial, last name, and a domain name, it would have been more difficult.

In the screenshot (below), I typed pcampbell@xlbasics.com and pcosgrove@xlbasics.com in full… and Excel didn’t recognize a pattern.

When I dragged to select cells B2:B7 and clicked the Flash Fill button in the Data > Data Tools group, Excel attempted to generate the other e-mail addresses but incorrectly assumed that each e-mail address should start with the letter p followed by the person’s last name.

Flash Fill error

The solution, in this case, would be to manually type e-mail addresses for the first four people. After entering the e-mail address for Ken Holloway – fourth in the list – you would drag to select cells B2:B7 and click the Flash Fill button. As soon as Excel sees that Ken’s first initial isn’t “P”, Flash Fill will work properly because it can detect a pattern. This might seem inconvenient when your list is 6 names long – but typing 4 names to have Flash Fill generate 600 e-mail addresses will be a huge time saver!

The Source Data is Inconsistent

Source data that has been entered in an inconsistent way will cause problems for Flash Fill. In this example, the first two dates were entered in the month / day / year format. The last date was entered in the day / month / year format… and the month is abbreviated, it’s not a number. Excel won’t be able to return valid results.

Spreadsheet with dates entered inconsistently

Microsoft Excel is a registered trademark of Microsoft Corporation. XL Basics contains articles and educational content related to Microsoft Excel but is not affiliated with Microsoft in any way.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.