Who knew that a single keystroke can save you so much time!
Flash Fill can be a real time saver when entering or manipulating list-based data in Excel and it’s a feature that, despite having been part of Excel for almost 10 years, isn’t known to many users. Take the data in the below screenshot….
I needed to merge columns A & B into a single column (column F). Yes I can use CONCATENATE or CONCAT. Yes I can use Power Query. But it’s quicker to type “Shannon Parr” into F2 and press the magic keyboard shortcut.
I needed to extract the street name from each address and place them into column G. I could have used Text to Columns. I could have used Power Query. I could have probably come up with a convoluted function. But instead I typed “Marcus Street” into G2 and pressed the magic keyboard shortcut.
I needed to display the telephone area codes in column H with parentheses. Again I could have used Power Query or a combination of LEFT and CONCATENATE but I chose to type “(256)” into H2 and, you got it, press the magic keyboard shortcut.
Finally, the “dates” in column E aren’t really dates. They look like dates but they’re text entries. Is that a problem? It is if you need to sort the list by DOB or calculate ages. So I need to convert them to real dates. Again I could have used Power Query. I could have used Text to Columns (which is pretty cool for text-date to real-date conversion). I could have used a function. But I chose to type “17/8/1994 “into I2 and press the magic keyboard shortcut.
And what is this magic keyboard shortcut? CTRL + E. It’s the shortcut key for Flash Fill. For more details watch the short video below
Use Flash Fill to Combine Text from Multiple Cells
Enable or Disable Automatic Flash Fill
Use Flash Fill to Combine Cell Values and Text
Use Flash Fill to Extract Part of a Cell Value
Use Flash Fill to Reformat a Telephone Number (or any Other Text)