Excel Tip – Flash Fill

Here’s your Monday Excel tip to help you become an Excel whiz!

Excel 2013 has a great new 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.  Unlike a formula, the new columns are static, so if the original information is updated, the data won’t change.  If you need to have the new columns update, then Text Formulas are the better choice. 

In the following example, flash fill was used to complete all of the information after the 1st row was complete:

To use the Flash-fill:

  1. Type in the pattern of the information you wish to extract.  It must be in a column next to the original data.
  2. Press Enter
  3. From the ribbon, select Cells > Fill > Flash Fill or press CTRL + E

In the following examples, names of individuals were imported into a single column. The Flash Fill can be used to separate the names into 3 columns, but generate an email address, based on the pattern in the first cell of the column.

Enter the data in the first cell. Press Enter, then CTRL + e
The first name is complete.

Auto-fill is used to fill in the rest of the data, including:

This feature can be used in a lot of different situations. This YouTube has 20 more ideas of how you can use Flash Fill.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: