Excel 2016's 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 appears in the new column, literally in a flash (thus, its
name, Flash Fill).
The second Excel 2016 detects a pattern in your initial data entries, the rest of the entries in that series immediately appear in blank cells in rows below that you can then enter with a single keystroke. And the beauty is that all this happens without the need for you to construct or copy any kind of formula.
The best way to understand Flash Fill is to see it in action. In the following figure, you see a new data table consisting of four columns. The cells in the first column of this table contain the full names of clients (first, middle, and last), all together in one entry. The second, third, and fourth columns need to have just the first, middle, and surnames, respectively, entered into them (so that particular parts of the clients' names can be used in the greetings of form e-mails and letters as in, "Hello Keith," or "Dear Mr. Harper,").
Rather than manually enter the first, middle, or last names in the
respective columns (or attempt to copy the entire client name from
column A and then edit out the parts not needed in 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:
The second Excel 2016 detects a pattern in your initial data entries, the rest of the entries in that series immediately appear in blank cells in rows below that you can then enter with a single keystroke. And the beauty is that all this happens without the need for you to construct or copy any kind of formula.
The best way to understand Flash Fill is to see it in action. In the following figure, you see a new data table consisting of four columns. The cells in the first column of this table contain the full names of clients (first, middle, and last), all together in one entry. The second, third, and fourth columns need to have just the first, middle, and surnames, respectively, entered into them (so that particular parts of the clients' names can be used in the greetings of form e-mails and letters as in, "Hello Keith," or "Dear Mr. Harper,").
 |
| Data Table containing full names that need to be split up in separate columns using Flash Fill. |
- 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 only have to type the first letter of the next name for Flash Fill to get the picture.
- In Cell B3, only type J, the first letter of 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.
- Complete the entry of Jonas in cell B3 by pressing the Enter key or an arrow key.The moment you complete the data entry in cell B3, the First Name column's done: Excel enters all the other first names in column B at the same time
