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!
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.
Step #1 – Select cell C2, type Paul Campbell and press Enter.
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.
Step #3 – Press Enter. Each cell in column C displays a 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.
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.
Step #1 – Select cell C2, type Paul and press Enter.
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.
Step #3 – Press Enter. Each entry displays a person’s first name from column A.
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.
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.
Step #1 – Select cell B2, type paul.campbell@xlbasics.com and press Enter.
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.
Step #3 – Press Enter. The entire list of e-mail addresses is displayed as a full name in column B.
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 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.
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.
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.
