Transform Data Fast with Excel Flash Fill

Excel Flash Fill, the un-formula filler, formatter, and concatenatorYou can easily use Excel’s Flash Fill tool to transform data fast, without formulas.

Did you catch that? Without formulas!

Flash Fill has been around a few years, but few people, including auditors, seem to be aware of it.

This tool is so easy to use, you could learn it AND teach it to your mom in 4 minutes. Really.

I like to describe Flash Fill as Text to Columns on steriods with concatenation mixed in.

They could have also named it Flash Format, because it does formatting too.

So what does Flash Fill do?

It allows you to extract selected portions of data from one column to another and format that data.

For example, Flash Fill can extract “Satish” out of “Bill Satish”, or can format “4184658901” as “(418) 465-8901”.

You can even add numbers using Flash Fill. Without ever entering a formula!

Learn Flash Fill Fast

Basically, all you have to do is create a blank column, and enter 2 examples of the data you want extracted from the data that already exists in other columns. Then click the Flash Fill icon, and Excel figures out the rest.

Assume column A contains first and last names of your users, but you want only the last name in a new column. Cell A1 contains ‘Bill Satish’, so type ‘Satish’ in cell B1 on the same row. Cell A2 contains ‘Siri Apothke’, so type ‘Apothke’ in cell B2.

Then click in blank cell B3, and on the Data tab, click the Flash Fill icon. Excel does the rest, filling B3 and the rest of column B with only last names.

This is only a SIMPLE example of what the tool can do. You won’t believe EVERYTHING it can do unless you watch 3 quick videos.

Quick Video

I found the Excel 2013 Flash Fill  videoes online at https://support.office.com/en-us/article/Use-AutoFill-and-Flash-Fill-2e79a709-c814-4b27-8bc2-c4dc84d49464 ; you can also access them via Excel Help.

Do it now; don’t put it off until later!

And please let me know what you think.

Finally, if any of you have EVER used Flash Fill before, let me know. I’ve never met anyone who has heard of it or used except the person that told me about it….

Check out my Excel Basic Data Analytics series.

 

2 Comments

Filed under Audit, Data Analytics, Excel, How to...

2 responses to “Transform Data Fast with Excel Flash Fill

  1. billy

    Before you click on the Flash Fill icon, you need to position the cursor in cell B3 THEN click Flash Fill. In other words, click in the next open cell under your 2 examples, then click the icon.

    Liked by 1 person

Leave a Comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s