Excel Tip of the Week #411 - Revisiting Flash fill

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:

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:

We can then just press Enter and the rest of the cells will be filled in:

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:

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’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:

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:

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.

• Previous post – Installing VBA redux
• TOTW index
• Next time – Working with large numbers of sheets!