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