– Ben Martens


Flash Fill

If you spend much time in Excel, you have probably found yourself doing some repetitive tasks to clean up a column of data. Or maybe instead of doing it manually, you might craft some formulas to do it for you. If you’re just doing a one-off cleanup, make sure you consider the Flash Fill feature in Excel. I recently used this in a demo to my team at work and many people in the room were surprised by it so I figured there were probably a few of you reading this who could benefit from it as well.

The basic idea is that you have one or more input columns and an output column. You provide an example or two of what the output should be and then you click the Flash Fill button. A magical wizard in your computer figures out the pattern and repeats it for the rest of the column. It really does feel like magic the first couple times you use it. Sometimes the algorithm gets it wrong for a few cells so you can just make a correction and it will update it’s algorithm and redo any cells that need it.

This video has a bunch of great examples:

If that’s not enough for you, check out The tech behind Flash Fill is open sourced out of Microsoft Research and this is one of the sample sites they have created. If you want to use the SDK, you can get it from GitHub.

Office 16

powerqueryexcel2016The next version of Office is now public. “But Ben, I already have Office and I hate buying new copies of it.” Never fear! Do you have an Office 365 subscription? If so then you get this upgrade for FREE. And if you don’t have a subscription, this is a good reason to consider it. Unless you have a single computer in your house and you never upgrade Office, the subscription is a great deal. You get five installs with free upgrades, 1TB on One Drive, and some other perks forĀ  100/year. There’s no more worrying about whether or not the upgrade is worth it for you. Pretty slick!

I normally don’t get too worked up about Office upgrades, but I’m really excited about Excel 2016. You may remember that I spent the vast majority of my time at Microsoft on Power Query and the projects that led up to it. Power Query now ships as a core feature of Excel 2016! Click the Data tab and look in the “Get & Transform” section. It’s awesome to have my code running on hundreds of millions (or billions?) of desktops. I know that it’s not a feature that most people will use, but it’s still cool to be included.