At a glance
Question
I have heard about flash fill in excel 2013 – is it worth using?
Answer
Flash Fill was added to Excel 2013, and it seems like magic when you first start using it. In fact, it sounds like a new superhero – and, yes, it is worth using, but it does come with a few disclaimers.
The idea behind Flash Fill was born on a plane trip in the US when a Microsoft researcher and a businesswoman sat together. The businesswoman asked if Excel could merge two columns, first name and last name, into a third column to create the full name in one cell. That got the researcher thinking – and the outcome was Flash Fill.
Show me the data!
Flash Fill examines existing entries in tables and does pattern matching between the data and your entries. Those patterns are used to populate blank cells within a range in a column. It can work automatically and it can also be run on a selected range using the shortcut Ctrl + E. See Figure 1 for an example of how Flash Fillworks automatically.
Columns A, B and C were already populated. In cell D2, I entered Tan, Susan Mary. This is the layout I required. I then just typed a capital J in cell D3 and Excel displayed the suggestions underneath in the light grey font, as per the top image in Figure 1. When I pressed Enter all the blank cells were populated as per the bottom image in Figure 1.
Note: All entries in the first three columns are in lower case. The layout I wanted included capitalised names and a comma following the surname. That structure was maintained in all the populated cells. The examples used here are short, but Flash Fill will work on thousands of rows.
Formula solutions
Much of what Flash Fill does can be done via formulas – but these formulas do tend to be reasonably complex and many users are unaware of the functions and techniques required to work with text. Flash Fill provides an easy alternative that requires no formulas.
Flash tips
To get the most out of Flash Fill, follow these guidelines:
- Use a table layout with column headings formatted as bold.
- Don’t have blank rows or blank columns in your table. It’s OK to have a heading with no data underneath.
- Provide at least two entries that define your required outcome. Sometimes, as in Figure 1, Excel will display the Flash Fill entries as you type the second entry. In simple cases, you can get away with only providing one example, but providing a few more examples can improve the pattern matching.
- You can correct the provided entries, and this will be used by Flash Fill to correct the rest of the range – see Figure 4.
- Work from left to right populating columns. Having columns to the right of your target column can stop Flash Fill from working.
A dialogue is displayed if a pattern cannot be identified when you use the Ctrl + E keyboard shortcut.
Downsides
Flash Fill only works in Excel 2013 and later versions, but once the data is entered it is visible in all versions.
Note: Excel 2016 has been released. Flash Fill will quickly populate blank entries for you, but if you add extra data to your table you will need to reapply
Flash Fill to extend the entries. This is not a great problem, because there is a keyboard shortcut to apply Flash Fill. Unfortunately, if you have too many examples it can affect the accuracy and you may need to reapply to the whole column with just a few entries.
Because Flash Fill populates cells with entries, users can be confused about whether they need to enter data into a previously Flash Filled column. Most users are unaware of Flash Fill.
Further examples
You can be creative with Flash Fill, and if you provide enough examples it can amaze you. I have included screen shots with commentary to explain how you can use Flash Fill.
Fixing a generated system date
Some systems have date structures that Excel doesn’t recognise as dates. In Figure 2, we use Flash Fill to convert them into a date Excel can use. I entered the dates in cellsB2 and B3 to demonstrate the pattern. I then selected the range B2:B6 and pressed Ctrl + E. This populated the other dates.
Extracting a number
If you have a number within a code and the number has some meaning and you need to extract it, it can be difficult to write a formula to extract just the number. Flash Fill does it easily, as can be seen in Figure 3.
I entered the numbers in cells B2 and B3 and selected the range B2:B5 and pressed Ctrl + E and Flash Fill did the rest.
Tell Flash Fill what to do
Sometimes Flash Fill jumps the gun and finds the wrong pattern. If that happens, you can correct the result and it will correct the other entries automatically.
To create a username with the first letter of the first name followed by the last name, we can teach Flash Fill what we want, as can be seen in Figure 4.
I entered hjones in cell D3 and selected the range D2:D5 and pressed Ctrl + E. The pattern identified was to put an “h” in front of the last name (see centre image in Figure 4). I corrected cell D5 and the other cells magically updated as per the bottom image in Figure 4.
Should you use Flash Fill?
Flash Fill is ideally suited to assist in ad hoc reporting situations, as it is quick and easy to use and requires no formulas. It can be helpful for those who don’t have formula skills but still have to work with data. Having said that, Flash Fill is ultimately a manual process, and you generally try to reduce the number of manual processes in your Excel files. For more regular and automated reporting solutions, you may want to create formulas.
The companion video and an Excel file may assist your understanding.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia.
