Flash Fill- Your new best friend?

Flash Fill is a new tool in Excel 2013 and higher that will make your life much easier. If you have a lower version of Excel, see what it can do anyway. It is a good idea to upgrade!

Flash Fill allows you to show Excel what you want to happen, and Excel will try and identify a pattern and replicate it. The easiest way to explain it is with examples.

To follow along go to 1.1

As shown below, we have a list of names that are missing the space between the first name and the second name. The only unique identifier is that the surname starts with a capital letter.

In the adjacent column, cell B8, we have typed how we would like it to appear with the space in the correct place. All you need to do now is highlight this column (B8 to B21), go to the DATA tab and click on FLASH FILL (or click CTRL + E which is the shortcut).

Flash Fill

Excel will immediately try and replicate what you did by analyzing cell A8 and what you typed in B8 to see what the common area is. It then replicates it to the other cells as shown below.

Flash Fill

The example above was perfect, but Excel won’t always be able to identify a pattern with just one example. If it doesn’t get it totally correct or it leaves some blanks, you can type more examples and repeat the Flash Fill until it understands the pattern.

So in the example below, we want to create a single name (first name, initial and surname) in a single cell out of 3 adjacent cells. Note that in column B we don’t always have a middle name.

When you type in cell D44 what you want to see and do the Flash Fill, Excel will leave blanks where it can’t apply the pattern.

If Excel leaves gaps it means it couldn’t decide what to do. You need to go to one of the blank cells and type in what you expect to see in that cell. So in D46 I would type Al Bino and then Excel would use that pattern to fill in the other blanks.

Flash Fill

Similar logic applies if it appears that Excel has got it totally wrong.

If you type the first cell and run Flash Fill and it doesn’t seem to work,

  • type what you expect to see in the second cell and
  • run Flash Fill again over the whole column.

The more examples you give the better it will be.

Note however that Flash Fill is not perfect. It will save you lots of time but you still need to do a quick check that it has got it right.

 

Back to: Intermediate Excel Course > Ultimate Excel Tool