Transform Data Fast with Excel Flash Fill

You 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.

 

Advertisement