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:

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:

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

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:

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:

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:

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:

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:

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

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:

From the Cells panel on the Home ribbon at the top of Excel, click the Delete item, From the menu, select 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:

Add some new headings and you're done:

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:

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 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.