Technology and the occasional justified rant


Excel 2013 – flash fill

Office 2013 offers a new look ‘n’ feel, improved integration with SharePoint, and a new cloud-based deployment model – but one of the other key aspects is ease-of-use… making some of those common tasks easier to accomplish. After demoing Excel 2013 a number of times, I can tell you that flash fill is one the audiences’ favourite features.

I’m sure we’ve all seen this scenario – you have a chunk of data, but it’s not in a workable spreadsheet format. Instead of the data being spread across columns, it’s all in one. How do you easily separate it out? Do you write formulae based on the string lengths and the position of the delimiters? How tedious.

When it comes to ‘basic maths Excel beats the human brain – try multiplying 376 x 923 in your head or on paper, and you won’t be surprised to know that Excel can do it faster. There are however some things that the human eye and brain can work out – look at this data and you’ll immediately see the pattern… but you’ll have to give Excel a little bit of help.

This where flash fill comes in. Step 1 is to provide Excel with a value – the first name is Dave, so I’ll type Dave into a cell next to the data block.

The next name is Sue, so I’ll type that underneath… and as soon as I’ve typed ‘S’ Excel gets the idea and offers to fill in the rest of that column. Press [Enter] and we’re done. Easy as that, no referring to the formula help.

From here on it’s the same for the other columns, but we don’t have to do them in the same order – even though the department is the last item in the chunk of messy data, I can do that column next.

…works for Microsoft as a Global Account Technology Strategist. In a former life he worked for the Lotus brand within IBM for many years. Married with one daughter and two dogs, lives in Camberley (Surrey, England), plays the guitar to a mediocre standard, and runs 10 kms and half marathons at an average speed. That’s it really.


Darren Adams • 27 November 2012

Previous Post

Next Post


  1. Vitor Pereira 27 November 2012 - 3:57 pm Reply

    Flash fill seems like a great addition but the answer to this question:

    “How do you easily separate it out? Do you write formulae based on the string lengths and the position of the delimiters? How tedious.”

    …is, you use the “text to columns” feature that’s been there since Excel 5.0 (1993) I believe. Or I’m I missing something?

  2. Darren 27 November 2012 - 5:38 pm Reply

    Yep, you can use that feature. But, bearing in mind this is a simple example, flash fill is more flexible and usability tests showed that users preferred it.

    • Vitor Pereira 28 November 2012 - 10:33 am Reply

      You’re absolutely right. Yes, flash fill is more flexible and yes, users will probably find it better and, as I said, flash fill is a welcome addition but suggesting that before that users add to tediously type “formulae based on the string lengths and the position of the delimiters” is a little to much. It’s not like there was nothing there before, is it?

      • Darren 28 November 2012 - 10:54 pm Reply

        Okay, point taken.

Leave a Reply

Your email address will not be published / Required fields are marked *