05 November 2012

Weekly Dynamic: Flash Fill in Excel 2013

Microsoft Excel 2013 has a great new feature for manipulating data. It's called Flash Fill and here is how it works.

Let's say that I have last name, first name data like this:

I want First Name Last Name no comma. I can write a formula to split and change the data. I can use text to columns to split the data at the comma and bring it back together with a formula. Both options require some work.

With Flash Fill, I go to cell B1 and type Mark Polino to tell Excel what I want my data to look like. When I go to B2 and start to type Tony Romo Excel 2013 figures out the pattern and offers to make the change to the rest of the column.

Hit Enter and Excel 2013 finishes the pattern filling in the rest of column. This is really cool. I've found that occasionally it takes a couple of examples for really complex patters but common ones like name manipulation take very little work.

One caveat, Excel just makes the change, it doesn't create a formula so changing the source in Column A doesn't change the result after the Flash Fill has run.