Skip to Main Content

 Indiana University


Microsoft Excel 2013: Using Flash Fill

A new feature in Excel 2013 called Flash Fill allows the user to separate or combine data within entire columns based on the pattern that Excel picks up from the other columns. You can also use Flash Fill to change the case of text. As long as Excel can pick up a pattern, it will work to complete the column for you.

Note the first and last name columns below:

FlashFill

Suppose you would like to join these columns so that the full name will appear in a separate column.  To make the new column with the full name, insert the cursor in the third column and type the first and last name for Bud Roberts. Press ENTER and begin typing the second name. You will see a transparent menu with a preview of suggested names:

FlashFill_complete

Press ENTER to finish the column. You can also use the Flash Fill command on the DATA tab of the Ribbon.  In this example, the First Name and Last Name columns do not have to be adjacent to each other, but the column using the Flash Fill must be next to one of these columns for Excel to recognize the correct pattern. This same technique works when separating text within columns:

FlashFill_SeparateData

The data must be entered consistently; for example, if some first names had a middle initial and some did not, you might get some unexpected results. If you did not want to use the Flash Fill, press the ESC key and keep typing to ignore the transparent drop-down list.

NOTE: Even though the “Convert Text to Columns Wizard” can still be used to separate text into columns, and the CONCATENATE function can be used to join text,  the Flash Fill method  is much quicker and avoids placing any formulas within the cells.


Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.