#Excel Flash Fill in action with 16 examples

Excel Tip - Flash Fill

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

Excel Tip - Flash Fill 1.1

Example 2 – Similar to example 1 with no delimiter but CamelCase

Excel Tip - Flash Fill 2

Example 3 – Get the 3rd (or Nth) value from CSV

Excel Tip - Flash Fill 3

Example 4 – Separate Text from Value and vice versus

Excel Tip - Flash Fill 4

Example 5 – Swap the position of First Name and Last Name

Excel Tip - Flash Fill 5

Example 6 – Replace the coma with Space

Excel Tip - Flash Fill 6

Example 7 – Add Initiate in the middle

Excel Tip - Flash Fill 7

Example 8 – Change all texts to lower case

Excel Tip - Flash Fill 8

Example 9 – Change all texts to UPPER case

Excel Tip - Flash Fill 9

Example 10 – Change only Last Name of UPPER case

Excel Tip - Flash Fill 10

Example 11 – Get Initiate, and then add it as a prefix

Excel Tip - Flash Fill 11

Example 12 – Get Initiate, and then add it as prefix without the helper step

Excel Tip - Flash Fill 12

Example 13 – Get Initiate, and then add it as suffice

Excel Tip - Flash Fill 13

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

Excel Tip - Flash Fill 14

Example 15 – Getting value from the inconsistent pattern

Excel Tip - Flash Fill 15

Example 16 – Getting value from the super messy pattern

Excel Tip - Flash Fill 16

Are you convinced now Flash Fill is simply amazing?  However, in order to have Flash Fill worked well, you need to:

  1. Study your data and find the pattern
    • Flash Fill works well when there is a consistent pattern
  2. Input examples next to the data (table)
  3. Review the suggested results
  4. Teach” Excel with correction, when the suggestion is not correct
  5. 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.

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

2 Responses to #Excel Flash Fill in action with 16 examples

  1. 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!

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.