Introduction
Flash Fill is a step up from Auto Fill that you probably use or that just happens to you!
Flash Fill is smart and what it does is that it allows you to, for example, combine or split the contents of cells in which Excel can deduce a pattern. Such as the case where we download some data from the internet or from a badly formatted Word file and it looks like this:
Row 1 Afghanistan 1870 168.4
Row 2 Afghanistan 1880 165.7
In this case I want to split the cells so that the country name in is one column, the year is in the next column and the value is in a third column.
You are right: we can use =LEFT() … MID() and RIGHT() to do these things but now the question is: why bother?
I have created some GIFs for you to see examples of what you might do. But first, turn on Flash Fill or even turn it off. This screenshot shows you the setting on my laptop: Flash Fill is turned on. Actually, it should be turned on by default.
File .. Options … Advanced … Scroll down to Editing Options … there it is! Check or uncheck!
Try this on some of your own data: my data on the heights of men from 1870 to 1980 come from the ourworldindata.org web site where there is a mine of data just waiting for you!
Splitting column A into three separate columns: Country … Year … Height
The column header is needed for reference of course!
Make sure you are using Flash Fill in the column next to the original column: the contiguous column
Because Flash Fill is already installed, it did what it had to do … I typed Afghanistan in row 2 and pressed Enter. Then I started typing Afghanistan in row 3 and as you saw Flash Fill anticipated what I was doing and suggested a solution … it was correct so I pressed Enter again and Bob’s your Uncle. Done!
Move the Country column out of the way now and Set up the next contiguous column for Year:
The same story.
Type in the year 1870 for the first of the Afghanistan rows and press enter
Start typing 1880 for the second row and Flash Fill anticipates your needs.
There should be no problems here so accept everything and then move the Year column out of the way. Prepare the Heights column contiguously!
Type 168.4 in row 2 then 165.7 in row 3 and by this time Flash Fill will probably have anticipated your needs. You can press Enter. However, notice in row 10 that it didn’t quite work and it gives you 1930 169 instead of 169. That’s because 169 is not the same as 169.0 and it breaks the pattern that Flash Fill has found.
Don’t panic!
Go to row 10 and type 169 … Flash Fill now understands and corrects row 10 AND row 15 AND row 29 …
Move the Height column to be where you want it and that should be it. Done!
Combine or Concatenate
On you own, why not concatenate something now rather than splitting it? Flash Fill can do that for you! Why not experiment with your new Year and Heights columns and do this:
Imagine Year is in Column E and Heights are in column F then in cell G1 Type Year Heights
Cell G2 Type 1870 … 168.4 or 1870 – 168.4 As you wish
Cell G3 Type 1880 and by this time Flash Fill will probably have spotted what you want to do and it will have concatenated the two columns.
Flash Fill can be a real time saver and it’s worth the tiny amount of effort needed to learn it
Flash Fill Button
Just in case things don’t go according to plan, there is a Flash Fill button on the Data tab that you can press to force it to work … Alternatively, if you want to turn Flash Fill off and use it manually, you will need to use that button.
Duncan Williamson
Excel with Excel Master 