The Joys of Excel Flash Fill

Flash Fill in Excel can make your life a lot easier, if you have to manipulate values in a cell. With Flash Fill, Excel tries to guess what you want to do. Let's have an example.

Example One - extract product IDs from a cell

Suppose you had a spreadsheet like this:

An Excel column with product IDs

However, all you want is the product IDs at the start, the letters. Is there an easy way to extract just the letters? Yes - use Flash Fill.

Create a simple spreadsheet like the one in the image above. Now click inside cell B2. Enter the first of the letters, ABC:

Product ID enetered into a cell

Press the enter key on your keyboard and you'll see cell B3 selected:

Cell C3 selected on a spreadsheet

To do a Flash Fill, hold down the CTRL key on your keyboard. Keep the CTRL key held down and press the letter E on your keyboard. Excel Flash Fills the rest of the product IDs for you:

Flash Fill in Excel

Notice the small box that's appeared in the bottom right of the selected cell (B3). Click the arrow on the box to see a menu:

Menu asking to Accept Flash Fill suggestions

Click the item that says Accept suggestions.

If you want, you can extract the numbers to the C column. Click inside cell C2. Enter the first set of numbers, which are 123. Press the enter key on your keyboard to move down to cell C3. Now press CTRL + E again to have Excel perform a Flash Fill. You should see the rest of the numbers appear in cells C3 to C5:

Using Flash Fill to extract part of a product ID

Notice that Flash Fill has even added a column heading of ID in cell C1. It's guessed this because the heading in cell A1 (Product ID) also has a space between it. Flash Fill is grabbing all the characters after the space.

 

Example Two - extract First Names and Last Names from a cell

Suppose you have a series of names in the A column, and that the first and last names are all typed in the same cell:

First and Last Names in a single Excel column

What you want to do, however, is to have the first names in the A column and the last names in the B column. Is there an easy way to do this? Yes - use Flash Fill!

Create a simple spreadsheet like the one in the image above. Click inside cell B2. Enter the first name of Ken. Press the enter key on your keyboard. This will select cell B3. Perform a Flash Fill by holding the CTRL key and pressing the letter E. Excel will finish adding the first names to cells B3 to B7:

Flash Fill has extracted to first names to the B Column

Now click inside cell C2. Enter the last name Carney. Flash Fill the rest of the last names down to cell C7:

Flash Fill has extracted to last names to the C column

Notice that Flash Fill has added the heading Names to cell C1. You can delete this, if you like. (Just click inside cell C1 then hit the delete key on your keyboard.)

We can now delete the entire A column, because we don't need the full names. Highlight the entire A column by clicking on the letter A column heading:

The entire A column highlighted

From the Cells panel on the Home ribbon at the top of Excel, click the Delete item, From the menu, select Delete Sheet Columns:

The menu item Delete Sheet Columns

When you click on Delete Sheet Columns you'll find that the old A column disappears. The B and C columns shift to the left, and you're left with new A and B Columns:

First and LAst names in two separate columns

Add some new headings and you're done:

Headings added to the first row

 

Example Three - append text

You can use Flash Fill to append some text to values in a cell. Suppose you had a list of pictures in the A column of a spreadsheet:

Picture file names in the A column of an Excel spreadsheet

However, none of these picture names have a file ending. Suppose you wanted to add .jpeg to the end of your picture names. Is there an easy way to do that? Yes - use Flash Fill!

Create the simple spreadsheet above. Click inside cell B2 and enter pic1.jpeg. Press the enter key to move the selection down to cell B3. Now press CTRL + E. Excel will Flash Fill the rest of the file names:

Flash Fill to add file types to the end of file names

Flash Fill can save you a whole load of time, and is well worth getting to grips with. Especially if you're doing text formatting on cell contents.

In the next lesson, you'll learn about Tables in Excel.