Excel’s Amazing

Flash Fill

Excel’s Flash Fill (introduced in Excel 2013) is one of the greatest time-saving features when it comes to fixing data.

Flash Fill allows you to “fix” common data format issues like splitting text, merging text, date extraction, text replacement, formatting and much, much more.

Flash Fill provides an alternative to learning many functions or features used to reshape data, such as:

Flash Fill is located on the Data tab in the Data Tools group and can also be invoked by pressing the CTRL-E keyboard shortcut.

Let’s look at just a few examples of how Flash Fill can aid in reshaping your data.

Splitting Text

If we were to receive a list of names where the first and last names are both stored in a single cell, we can split the names by selecting the cell to the right of the first name in the list and typing the first name.  After you press Enter, press CTRL-E to generate a list of the remaining first names.

We can now select the first cell to the right of the newly created first names list and type the last name.  After you press Enter, press CTRL-E to generate a list of the remaining last names.

PRO TIP: Think of it this way: type a version of the data the way you WISH it were to appear, then press CTRL-E.

Concatenating Text

To concatenate text, such as first and last names into a full name, type the first full name then press CTRL‑E.

Email Addresses

If you have a list of names and you need to generate email addresses for the names, type the first email address then CTRL‑ENOTE: This process will NOT consider duplicate email address as problematic; these will need to be adjusted manually.

Changing Case

If you received a list of names in all lower case letters and you wish to have the first character of each name capitalized, type the first name in the proper case format and press CTRL‑E.

Initials

If you received a list of names and you wish to generate a list of initials, type the first user’s initials and press CTRL‑E.

Extracting Years, Months, and Days

To extract the year from a date, type the year of the first date and press CTRL‑E.

PRO TIP: If you are extracting months and days, Flash Fill can become confused when encountering a day and month that have the same number (i.e. 9/9).  In cases like this, select a different row in the data that does not have the same day/month value and press CTRL‑E.  The list will Flash Fill in both directions.  Any case where the first entry in the list confuses Flash Fill, create your example on a different row that may not be as confusing to Flash Fill’s programming.  This may take some trial and error.

Important Points

about Flash Fill Operation

  1. Flash Fill is NOT dynamic.  Once the list is generated, there is no connection back to the original data.  Any changes in the original data will not carry forward to the successive lists.
    If this behavior is required, you will need to invest some time to create more dynamic formulas using some of the functions mentioned in the list at the beginning of this lecture.

 

  1. The new list generated by Flash Fill must be on the same rows and directly to the left or right of the source column.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

NEW Course: Excel ESSENTIALS for the REAL World (The Complete Excel Course)

From Excel Beginner to Professional

Learn Excel from Scratch

OR Improve Your Excel Skills to Become More Confident

Check out our best-selling course

Visit Course

Leave A Comment

Share This