by Dawn Bjork, MCT, MOSM | The Software Pro
When you have data in Excel you want to use differently, that is to combine multiple columns or extract just portions of the data, Flash Fill might be the solution. Although there are a number of functions that can get you to the same results, such as CONCAT or CONCATENATE to combine, or the LEFT, MID, and RIGHT functions to extract portions of the data, Excel Flash Fill may be an easier answer.
How Does Flash Fill Work?
With Flash Fill, Excel is looking for a pattern, and so, your data needs to be consistent. If you’re not getting the results you’re looking for, you might need to do some data normalization or clean up the data to remove inconsistencies such as removing a middle initial used in a name or eliminating a prefix or suffix. (Flash Fill was introduced with Excel 2013 and is available in more recent versions including Excel 2019, 2016, and Excel 365).
Flash Fill is easier than you might think. Let’s say you have a column of names that is formatted with the last name, a comma and space, and the first name. To get a column of only the first name, create a new column adjacent to the full name column. Next, type the first name found in the full name and hit [Enter]. Next start typing the first name for the next entry or two and Excel will automatically vertically populate the remaining names based on the pattern in the first few examples. If you’re seeing the answers you want, just press [Enter] to accept it. No other actions are needed although it’s helpful to double-check your results. Flash Fill not only works with text entries but also with dates and numbers.
With Flash Fill, the data you see isn’t going to change because there isn’t a formula or a link pointing back to the original data; it is actual data you can sort, filter, and edit. In fact, once you have the results you want, you could delete the original data. When you use the Excel Flash Fill option, one of the features that also displays is the Flash Fill smart tag. If you’re happy with the results, you can ignore this smart tag but this option is available to reverse the feature or even highlight the cells in case you wanted to copy this data to another location. Or, if you don’t want to Flash Fill the data, choose the Undo Flash Fill option under the smart tag or press [Esc].
Other Ways to Launch Excel Flash Fill
It isn’t necessary to do anything to make Flash Fill work because the feature is enabled in Excel by default. If for some reason it is turned off, you can turn it on by going to the File tab. Next, choose Options at the bottom and then pick Advanced. You’ll find the feature under the Editing options section: Automatically Flash Fill.
Flash Fill can be triggered automatically in other ways including options in the Ribbon and even with a keyboard shortcut. And, I happen to love keyboard shortcuts! The shortcut for Flash Fill is [Ctrl] + E. Let’s see how this works:
- Enter the pattern you want to extract such as a last name from the full name. The most common approach is to type a handful of examples in a column until Excel detects a pattern and triggers Flash Fill. Typically, we’d type a sample entry to demonstrate the pattern, press [Enter], continue with the next entry, and then either come back to the entries to launch the feature or look for it to be automatically triggered.
- As an alternative, instead of hitting [Enter], press [Ctrl] + [Enter] which completes the entry but keeps you positioned in the same cell. Next, press [Ctrl] + E, the shortcut for Flash Fill. And if Excel is able to find the pattern you have now filled to match that data.
You can also find the Excel Flash Fill feature in the Ribbon:
- Home tab > Editing group > Fill > Flash Fill, or
- Data tab > Data Tools group > Flash Fill
With Flash Fill, you now have the power to save time and quickly convert or extract your Excel data in a way that works for you.
IAAP works with the best trainers in the industry to ensure you have relevant, engaging, practical content at your fingertips. This blog is written by a speaker with a program in the IAAP Approved Programs database. Search by name or keyword to find their contact information and book them for your Branch or Region event.
If you’d like to attend one of these events, you can find one in your area here.
Dawn Bjork is The Software Pro®, a Microsoft Certified Trainer (MCT), and is a certified Microsoft Office Specialist (MOSM) Master, and Microsoft Office expert. Dawn is passionate about sharing smart and easy ways to increase your software productivity through her work as a conference speaker, software trainer, computer consultant, and author of 9 books. Discover more software tips, techniques, and timesavers at TheSoftwarePro.com. Dawn also shares oodles of daily tips on Twitter and Facebook.
IAAP Edge 