Finding Buried Data with Flash Fill in Excel 2013

In Excel 2013, Microsoft has introduced a new feature that makes extracting specific data from a row or column easy: Flash Fill.

Rather than creating a complicated formula or manually entering data, Flash Fill allows you to give Excel an example of what information you want, and it will try to copy it.

This can be used to get nearly anything you can think of: names, phone numbers, dates, etc.

To show you how this can be done, we pasted into a blank Excel workbook a list of the US Presidents and their term of office found online. To extract contextual data using Flash Fill:

  1. Select a cell next to the column (or row) from which you want to extract data.
  2. Enter a value that you want to extract to be used as an example. (For instance, we’ll enter “Washington” if we’re after the Presidents’ surnames, or “1789” for the first year of their term.)
  3. Select the range of cells that you want Excel to fill with the contextual data, making sure to include your first filled cell in the selection. Click HOME, , then FLASH FILL.You can also use the fill handle to Flash Fill. At the bottom-right corner of the cell, drag the fill handle .

    Then Click , and choose FLASH FILL.
  4. Either way, you will have cell entries that conform to a common style. In this case, we extracted the Presidents’ surnames from a list. You can try extracting their first names or the year they entered or left office.

Excel may not always be able to decipher what values you want to Flash Fill, but we’ve been impressed with its accuracy so far.

Have you been using Flash Fill in Excel 2013? If you have any tips or experiences to share? Please let us know in the comments!

For more handy tips on using Excel 2013, stay tuned for our upcoming Quick Reference Cards on Excel 2013 and the rest of the Microsoft Office 2013 Suite.