How to Use Flash Fill in Excel

How to Use Flash Fill in Excel

Excel 2013Excel 2013’s 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.

The series of entries appear in the new column, literally in a flash (thus, the name Flash Fill), the moment Excel detects a pattern in your initial data entry that enables it to figure out the data you want to copy. The beauty is that all this happens without the need for you to construct or copy any kind of formula.

Rather than manually entering first, middle, or last names in respective columns (or attempting to copy an entire client name from column A and then editing out the parts not needed in the First Name, Middle Name, and Last Name columns), you can use Flash Fill to quickly and effectively do the job. And here’s how you do it:

1. Type Keith in cell B2 and complete the entry with the down-arrow or Enter key.

When you complete this entry with the down-arrow key or Enter key on your keyboard, Excel moves the cell pointer to cell B3, where you have to type only the first letter of the next name for Flash Fill to get the picture.

2. In Cell B3, type only J, the first letter of Jonas, the second client’s first name.

Flash Fill immediately does an AutoFill type maneuver by suggesting the rest of the second client’s first name, Jonas, as the text to enter in this cell. At the same time, Flash Fill suggests entering all the remaining first names from the full names in column A in column B.

3. Complete the entry of Jonas in cell B3 by clicking the Enter button or pressing an arrow key.

The moment you complete the data entry in cell B3, the First Name column is done: Excel enters all the other first names in column B at the same time!

To complete this example name table by entering the middle and last names in columns C and D, respectively, you simply repeat these steps in those columns. You enter the first middle name, Austen, from cell A2 in cell C2 and then type W in cell C3.

Complete the entry in cell C3 and the middle name entries in that column are done. Likewise, you enter the first last name, Harper, from cell A2 in cell D2 and then type S in cell D3. Complete the entry in cell D3, and the last name entries for column D are done, finishing the entire data table.


Leave a Reply

Your email address will not be published. Required fields are marked *