Excel Flash Fill Is A Brilliant Time Saver

One of the best enhancements in Excel 2013, part of the newly-announced Office 2013 suite, is a simple option called Flash Fill. Rather than writing complex formulas to extract items of data from a single column, you can provide Excel with an example of what you want to do and it will copy it.

That's more easily explained with an example. Suppose you had a list of names in the form Firstname Surname and you want to extract just the surname into an adjacent cell. To do that, you type the first name in the first couple of cells. You'll see a greyed out version of what Excel thinks you want to do appear in the cells below. If that's correct, type Control-E for Flash Fill (or select it from the Data tab). Excel will work out the process you've applied and continue that for the remaining cells in that column.

It isn't a perfect solution for everything. If you have complex data where (for example) middle names appear sometimes but not all the time, you'll need to use the existing data splitting functions. But for basic tasks it's very impressive.


Comments

    Be nice if it recorded a formula that you could then tweak to suit. If it doesn't then how will it go across different versions of Excel?

      Simple, it saves the value rather than the formula making the file compatible with previous versions

        Two issues with that:(1) What if the person using the older version of Excel wanted to add more information to the spreadsheet and continue to use the formula? (2) What if the result it gives you is *really close* to what you want, but you know that it's only off by (for example) one character. If you could access the formula, it might just be a matter of adding a "-1" in there somewhere.

    I just spent about two hours playing around with this feature, using real data. I tried at least five different types of data and not once could I get it to work correctly. Sometimes it was close, but close usually isn't good enough. All of the example I've seen have been trivial (like the one shown here). In real life, data usually isn't consistent enough for this feature to work reliably. At least that's my initial impression. Maybe it will be improved in the final version.

Join the discussion!

Trending Stories Right Now