How to Use Flash Fill to Extract Content in Excel 2016

By Faithe Wempen

The Flash Fill feature in Excel 2016 enables you to extract data from adjacent columns intelligently by analyzing the patterns in that data. For example, suppose you have a list of e-mail addresses in one column, and you would like the usernames (that is, the text before the @ sign) from each e-mail address to appear in an adjacent column.

You would extract the first few yourself by manually typing the entries into the adjacent column, and then you would use Flash Fill to follow your example to extract the others. You could also use Flash Fill to separate first and last names that are entered in the same column.

To use Flash Fill, follow these steps:

  1. Make sure there are enough blank columns to the right of the original data to hold the extracted data.

  2. In the first row of the data, create an example of the separation you want by typing in the empty column(s).

  3. In the second row of the data, click in the cell in the column you want to populate.

    Create an example of the separation you want in blank column(s) to the right of the original data.

    Create an example of the separation you want in blank column(s) to the right of the original data.
  4. On the Home tab, click the Fill button to open a menu.

  5. Click Flash Fill.

    The data in the column you selected in step 3 is filled in.

  6. Repeat steps 3-5 as needed to populate additional columns.

Step 6 is necessary because you can only Flash Fill one column at a time. If you want to split out data from multiple columns at once, use the Data→Text to Columns command. Use the Help system in Excel to find out how to use that command.

The Flash Fill command populates the columns with data using your example.

The Flash Fill command populates the columns with data using your example.