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 more.
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.
For example, you need to sort a list by the last name, but you were provided a list of names where the first and last names are in the same cell. We need the first names to be in one column and the last names to be in a separate column.
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.
Think of it this way: Type a version of the data the way you WISH it were to appear, then press CTRL-E.
Flash fill can transform data based on a variety of situations, such as:
- Splitting data
- Concatenating data
- Extracting years, months, days, hours, minutes, or seconds
- Replacing data
- Rearranging data
And so much more. Flash Fill is a veritable wonderland of transformation possibilities.