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.
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.
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.
about Flash Fill Operation
- 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.
- The new list generated by Flash Fill must be on the same rows and directly to the left or right of the source column.
Feel free to Download the Workbook HERE.
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