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.