We don’t mean make them cry in a bad way. Let me set the scene. Microsoft have between 1-1.5 billion users of Office applications worldwide. The Office developers work really hard to make the applications intuitive so users can just get on and use them without needing to take a day’s training course in Outlook or Excel. However there’s a downside. Users tend to use new versions of Office in the same way they used the previous version so they don’t really see any immediate improvement and that’s part of the reason we see customer inertia; no-one takes the time to point out some of the fantastic things they can now do.
With new Office versions, there’s really two buckets of goodness – firstly, a bunch of new features and secondly, improvements. The improvements might include a single button which does what used to take the user 5 minutes and twenty clicks in the previous version. Or perhaps an improvement will stop users swearing so much! New features are great too but I find they’re best learnt organically; take a couple of minutes each day to look at a new feature and if it will help, then practice it but don’t try to learn every new trick in Office from day 1.
So we’re starting a series of very quick demos that you can easily emulate either to learn from or to repeat if you have customers that use Office. Welcome to number 1.
1. Download and open the sample Excel file. Once you’ve downloaded that, pat yourself on the back for ensuring you had up-to-date anti-malware installed and you can confidently download files from the great unwashed Internet.
2. You’ll see a simple table with five columns. Column 2 (Data) holds concatenated strings which we need to split out into the correct columns, so the Manager column will hold names such as John, Jenny and Bill and the Category column will hold the type of expense such as Advertising, Events and Digital Marketing. Some Excel users will look in the help for a string function that will work. Others will look in the ribbon and perhaps try out Text to Columns. Most users will see there’s only twenty table rows and just type or copy and paste. That’s how errors occur; one Bill might be typed with three l’s in it; we won’t notice and the reports will be wrong.
3. Click in cell D4 and type John. Hit ENTER to go to cell D5 and type Jenny. As soon as you’ve typed Jen you should see Excel volunteer the rest of the rows. It’s as if a little Excel intern has been watching and is now stepping into take over your work. How lovely. Hit enter to accept the Flashfill.
4. Now click in cell E5, type Events and hit ENTER. Hit CTRL+E to force Flashfill to evaluate the pattern at this point and you should see the suggestions. Press ENTER to accept.
5. Flashfill can do smarter things too. Click in cell F5 and type John heads up events. Then hit enter to move down into cell F6 and start typing Jenny heads up advertising. Flashfill will complete the rows for you, including respect to your capitalisation of the category name (keeping it lowercase).
Customers have asked at what point Flashfill is checking. You can be reassured that this is nothing scary. We’re all happy with autofill; type in 1 in a cell and 2 underneath and you can have Excel continue the numbering pattern t0 3, 4, 5, etc. Flashfill is just an extension of autofill, that’s all.
Sometimes Flashfill will not guess correctly first time in which case you ignore the greyed out suggestions and keep typing rows. Your little Excel intern will keep watching and at some point will guess the correct pattern at which point you just hit ENTER to save lots of typing! Sheet 2 includes an example of this. Click in cell C2 and type Adriana from Germany. Go to cell C3 and type Billy from United Kingdom. Flashfill is suggesting incorrect matches at this point but you just ignore it and keep typing in the rows. You will need to get down to row 5 (Damien from Germany) before the suggested pattern is correct and at this point you can accept it by hitting ENTER.
Flashfill is available as a Ribbon command too (on the Data tab) and is a feature of Microsoft Excel 2013 and Office 365 ProPlus.