ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week #411 - Revisiting Flash fill

Author: David Lyford-Smith

Published: 14 Sep 2021

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.

Hello all and welcome back to the Excel Tip of the Week! This week we have a General User level post in which we are taking a fresh look at the handy Flash fill data reformatting tool. This was first covered back in TOTW #124.

What does Flash fill do?

Flash fill is an AI-driven tool in Excel that can help complete simple data transformations. It takes an example of new, transformed data from you, and attempts to apply the same pattern to the rest of a column of data. Let’s take this example of phone numbers:

Excel screenshot
We want to reformat these into a more readable format with spacing. All we have to do is type the first in our desired format, and then start the second, and Flash fill will automatically suggest how to handle the rest:
Excel screenshot
We can then just press Enter and the rest of the cells will be filled in:
Excel screenshot

Note the little menu icon which has now appeared – you can use this to undo the Flash fill, or to highlight which cells it generated vs. those which you typed manually.

If Flash fill’s suggestion don’t automatically appear for you, try using Ctrl E or selecting the option from Home => Fill.

How can you do more with Flash fill?

Flash fill is based on AI, so it isn’t perfect – but it does do pretty well at figuring out patterns. One nice feature is that, if the pattern that Flash fill tries isn’t quite right, you can correct it line by line and, as you go, it will update and try to find the right pattern.

Let’s take this list of names and titles as our example:

Excel screenshot
We want to sort out the capitalisation and include any title in brackets. We type out our first one and then see what Flash fill manages:
Excel Screenshot
Excel’s first guess is to always have the brackets, which makes sense, but isn’t what we’re after here. But after pressing Enter, we can then re-enter Jessica’s name with no brackets. If we do that, Flash fill updates to:
Excel screenshot
This is a bit better, but we’ve over-corrected – now none of the names have titles beyond the first. But if we manually correct Paul:
Excel screenshot

Now we are sorted. The real strength of Flash fill is easily performing simple data transformations at speed. But it does have some weaknesses.

The main issue is that Flash fill is an opaque process – once you have stopped making tweaks to the data and moved on, there’s nothing left in the file to either note that you used Flash fill, or what exactly it did. This means it’s easy to miss a mistake, and hard to apply a correctly-working Flash fill to new data.

There is one similar function that’s worth investigating if you want the ease of Flash fill without these issues – loading the data into Power Query, and using “Column from Examples”. This feature similarly uses AI to try and figure out what you want, and similarly can be modified by providing more examples, but it creates an explicit and re-usable PQ transformation step at the end, making reviewing and re-using much easier. It’s not foolproof – I couldn’t get it to figure out the name pattern, for example – but it’s worth looking in to.

Take a look at all the examples and try them yourself in the attached file.

Join the Excel Community

Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.

Excel