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:
- Type in the pattern of the information you wish to extract. It must be in a column next to the original data.
- Press Enter
- 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.
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.