How To Use Flash Fill Auto Fill in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Spreadsheets often include a lot of repetitive data.

Sometimes it’s a sequence of dates. Or a specific series of numbers pulled out of longer sequences. It might be a database of email addresses.

Whatever it is, it takes a long time to get it all listed and formatted correctly…

… But Excel can make it much easier and faster!

Flash fill and auto fill are powerful tools that can seriously speed up your work in Excel.

Let’s check them out and see what they can do for you 🙂

Get your FREE exercise file

Before we get started, download the example workbook below.

It contains spreadsheets that you can use to follow along as we learn about flash fill and auto fill. And that makes it much easier to learn!

Download the FREE Exercise File

Download exercise file

Combining data with flash fill

Let’s open up the first sheet in the example workbook to see how flash fill can help you combine data in different cells.

As you can see, this sheet contains a column full of first names, a column of last names, and a column of company names. For our example, we’ll say that each of these people have the same style of email address: first.last@company.com.

We’ll use flash fill to get Excel to create those email addresses automatically.

First, we’ll manually enter an address. We’ll start with the first employee, Winfred Loakes of Fivespan.

Click into cell D2 and type “winfred.loakes@fivespan.com”:

prep-flash-fill

Remember that you’ll always need to enter one cell manually, or create it with a formula, to show Excel what you’re trying to do.

Kasper Langmann, Co-founder of Spreadsheeto

Now we’ll put Excel’s power to use.

Click the Flash Fill button in the Data tab of the Ribbon.

flash-fill-button

Once you’ve done this, Excel will do its best to carry on the pattern.

Let’s see how it did in our case:

flash-fill-results

Very impressive! Excel combined the first and last names with the company names to create email addresses.

You might notice, however, that the email addresses in rows 4 and 10 have spaces in them. The company names both consist of two words, and the spaces between those words were included in the email addresses. Let’s fix that.

Clear column D, and click into cell D4.

Type “kellina.swyn@twitterwirecorp.com”:

no-space-address

We’ve removed the space between the two words, but will Excel be smart enough to do the same for other two-word company names?

And will it still work with single-word names? Let’s find out.

Let’s find out…

Click the Flash Fill button.

flash-fill-no-space

Of course it is!

Excel learned from our removal of that space, and applied it to the two-word company name in row 10. And it also managed not to mess up any of the single-word company names.

Effectively using flash fill is often about finding the right way to show Excel what it is you want.

Kasper Langmann, Co-founder of Spreadsheeto

Extracting data with flash fill

Flash fill can do more than combine data: it can extract it, too. Let’s see how it work by going to the second sheet in our example workbook.

Here, you’ll find a list of first names, last names, and social security numbers (don’t worry, they’re fake). We can use flash fill to extract some of the data from the social security column.

Let’s say we want to get the first 2 and the last 4 digits of each social security number.

Click into cell D2 and type “41 / 1297” to tell Excel what it is you’re looking for.

flash-fill-extract

Then, like before, select cell D2 and hit the Flash Fill button.

flash-fill-extract-results

Not only did Excel grab the digits we want, but it also added the slash as a separator in every cell.

Flash fill is extremely powerful, and these two examples just show a bit of what it can do. Play around with it to see what others uses you can find!

Kasper Langmann, Co-founder of Spreadsheeto

Continuing a series with auto fill

You’ve probably used one of Excel’s most powerful features without even realizing it…

You can access the auto fill function by using the fill handle.

That’s the small square at the bottom-right corner of the box that lets you fill adjacent cells:

auto-fill

You might not realize just how powerful this feature is. Here’s a list of what it can do:

  • Copy cells
  • Fill cells from a series (as above)
  • Fill formatting only
  • Fill without formatting
  • Fill days
  • Fill weekdays
  • Fill months
  • Fill years

These are relatively self-explanatory, but we’ll go over them quickly so you can get an idea of what to expect.

Open up the third sheet in the example workbook, and you’ll see several rows of data that we’ll use to examine how auto fill works.

Click into cell A1, and use the fill handle to drag the cell a few spaces to the right.

auto-fill-copy

As you can see, auto fill has defaulted to copying the number. If we want to continue filling the series, however, we can click the Auto Fill Options menu:

auto-fill-options

From there, select Fill Series, and the numbers will change:

auto-fill-series

If you select multiple numbers, Excel will default to filling the series.

So if you have 2 in a cell, and 4 in the cell next to it, Excel will fill 6, 8, 10, and so on. No matter the pattern, Excel will fill it.

Kasper Langmann, Co-founder of Spreadsheeto

Now, let’s try the next row of data. As you can see, the first cell is formatted differently. Let’s use the two formatting fill options and see what happens.

First, click the cell and use the fill handle to drag it to the right. Excel will copy the cell, replacing the two that were there previously:

auto-fill-replace

If we open the Auto Fill Options menu, however, and select Fill Formatting Only, the formatting will be added to those cells without changing the values:

auto-fill-formatting

Take a guess at what Fill Without Formatting would do.

Now try it and see if you were right!

Kasper Langmann, Co-founder of Spreadsheeto

Finally, we come to the date in the last row of the sheet. Here, we’ll experiment a bit with auto filling dates.

Excel defaults to Fill Series, which does the same thing as Fill Days:

auto-fill-days

If you choose Fill Weekdays from the Auto Fill Options, however, Excel will skip over any weekends:

auto-fill-weekdays

You can use these functions to fill in written day names, as well. If you auto fill “Monday” to the right, you’ll get “Tuesday,” “Wednesday,” and so on. The Fill Weekdays options works, too!

Try this with written months and see what happens.

Kasper Langmann, Co-founder of Spreadsheeto

Selecting Fill Months will keep the day constant, but insert the next month in each cell:

auto-fill-months

And using Fill Years will keep the month and day constant, but advance the year in each cell:

auto-fill-years

Pro tip: use auto fill to the left to fill the series backwards

In these examples, we’ve used the fill handle to go to the right. This increases the number or date. The same thing happens when you use the fill handle to go down.

But if you use the fill handle up or to the left, the series fills in backwards.

Filling 1 to the left will get you 0, -1, -2, and so on.

Using the fill handle to drag August 1st to the left will get you July 31st, July 30th, and so on down the line.

Start saving tons of time

It might not seem at first like flash fill and auto fill are total game-changers, but once you start using them, you’ll see that they can save huge amounts of time. Almost everyone who works with spreadsheets needs to fill repetitive data.

And these two functions makes that process a whole lot easier.