Flash Fill was introduced in Excel 2013. After 6 years, it is still a hidden gem of modern Excel that most regular Excel users do not even know its existence. Whenever I show to my colleagues what Flash Fill is capable of, they are all impressed and think that this is their “Greatest Discovery” moment.
So in this post, I am going to show you in a flash what Flash Fill can do with 16 examples.
Here’s the video on my YouTube channel. Please turn on CC for English subtitles.
If you prefer reading to watching, you will see GIFs showing the examples below:
Note: CTRL+E is the keyboard shortcut for Flash Fill which had been used in all the GIFs in the coming demonstration.
Example 1 – Extract First Name and Last Name where coma is the separator
Example 2 – Similar to example 1 with no delimiter but CamelCase
Example 3 – Get the 3rd (or Nth) value from CSV
Example 4 – Separate Text from Value and vice versus
Example 5 – Swap the position of First Name and Last Name
Example 6 – Replace the coma with Space
Example 7 – Add Initiate in the middle
Example 8 – Change all texts to lower case
Example 9 – Change all texts to UPPER case
Example 10 – Change only Last Name of UPPER case
Example 11 – Get Initiate, and then add it as a prefix
Example 12 – Get Initiate, and then add it as prefix without the helper step
Example 13 – Get Initiate, and then add it as suffice
The following examples involve more than one pattern. It requires you to “teach” Excel with more correct examples.
Example 14 – Swap First Name and Last Name without coma, and add initiate as suffice
Example 15 – Getting value from the inconsistent pattern
Example 16 – Getting value from the super messy pattern
Are you convinced now Flash Fill is simply amazing? However, in order to have Flash Fill worked well, you need to:
- Study your data and find the pattern
- Flash Fill works well when there is a consistent pattern
- Input examples next to the data (table)
- Review the suggested results
- “Teach” Excel with correction, when the suggestion is not correct
- ALWAYS check your results, esp. when there is more than one pattern
Last but not least, explore Flash Fill on your own!
You may download a Sample File to follow along.
If you have any other wonderful use cases of Flash Fill, please share it in comments.
wmfexcel 
Wow! I use flash fill all the time but I was not aware of all these possibilities – great post! Definitely sharing this one with my team. Thanks!
LikeLike
Thanks. Glad you like it.
Indeed there should be more possibilities. Let’s test its limit. 😃
LikeLike