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.
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?
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.
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?
Okay, point taken.