How to Autofill in Excel

There are many ways you can speed up data entry in Excel. Using autofill can be particularly helpful because Excel can normally figure out patterns and the formulas that you’re going to want to enter. It can save you lots of time from having to manually enter values or formulas yourself. Below, I’ll show you how you can autofill values and formulas in various situations and how you can also use flash fill.

How to use autofill

Automatically filling in data is easy in Excel and it’s as easy as double-clicking. Let’s start with a very basic example. Here’s some data I’ve entered using a random number generator:

The formula in column C is just adding the corresponding values in columns A an B. It’s a simple formula but that doesn’t mean I want to enter it in repeatedly. To save me the the hassle having to do that, I’ve got a couple options.

The first is to double-click on the edge of the cell that has the formula in C2:

You’ll notice on the bottom right corner there’s a square there. If you hover your mouse over it your pointer will turn into a black cross. When that happens, double-click and excel will autofill the same formula all the way down to the rest of the rows that you’ve filled in:

You could also manually drag the corner down rather than double-clicking it.

Alternatively, if you want to specify how far down you want to autofill then there’s another approach you can take. Start by selecting your formula as well as how far down you want to copy it:

On the Home tab, in the Editing section, there’s a Fill button I can click on which will give me some options:

Clicking on the Down button will autofill the cells I’ve selected with the formula that I entered in the first row. The result is the same as when I double-clicked in the corner of the cell.

The main advantage of doing it this way is if you don’t want the formulas to copy all the way down to where your data ends. It’s not a situation I’ve ever come across and double-clicking is a lot more intuitive and a much simpler approach.

Filling in values

It’s not just formulas that Excel can autofill, it can even work on values. I’m going to add some numbers to my data set:

If I try and autofill by double-clicking on the corner of cell D3 this is what happens:

Excel didn’t figure out that I wanted to keep on incrementing by 1 and instead it just copied the number 2 all the way down. When it comes to autofill, you can help Excel by selecting more data:

I’ve selected the first two values in the data I want to autofill. Now, when I double-click and autofill from D3, here’s what happens:

Now that I’ve given Excel more of a pattern to work with, it’s figured out what I wanted to do. Even if I select an odd pattern like this:

Excel is still able to complete the pattern:

Autofill works also works with dates

Just like with numbers, Excel can figure out the pattern if I’m working with dates. Here’s a scenario where I’ve only got one date entered in: January 1

If I autofill the rest of the data, it’ll increment by one day:

If I don’t want to increment by one day, I can increment by month by giving Excel more data to generate a pattern from:

Now, using that selection, Excel knows to auto fill the remaining data by one month intervals:

Flash fill can even figure out your patterns without formulas

Now, let’s switch over to using flash fill, which can almost read your mind. In the following scenario, I have not entered any formulas in the data below:

The value in column C is supposed to be a combination of columns A and B joined together by a space. Without a formula, simply using autofill and double-clicking isn’t going to help in this situation, it’ll just copy the value in cell C2. This is where using flash fill can take you to the next level. If I select the value in cell C2, I can go back to the Fill button and this time select Flash Fill:

And here’s the result:

Incredibly, Excel’s filled in the remaining data and pattern with only one cell to work with and no formula entered. Even if I start entering the data manually, Excel will ofter to help me, showing me that it can fill in the pattern for me:

If I want to accept Excel’s help, I can just click on Enter and it will fill in the remaining cells. Excel doesn’t always get it right, but when it does, it can be a significant time-saver, especially if you’ve got lots of data to enter.

Why you may not want to use flash fill

Although flash fill can save you some time like in the example above, it won’t always work how you may hope. And relying too much on it can prevent you from learning how to use concatenate or other formulas that can make you more efficient in the first place.

It’s always better to use formulas and autofill as it’ll ensure that your logic remains intact exactly how you want it. And if you’re working on older versions of Excel that don’t have flash fill, you’ll be out of luck and finding yourself doing things manually again. There’s no substitute for a well-designed data structure that depends on formulas. You should always avoid hard-coding your data when possible.

And if you dump in more data into your database, you’re going to have to repeat the flash fill steps all over again. Unlike with formulas, Excel’s not going to update the values that you flash filled if you make changes later on. Flash fill can be helpful, but you shouldn’t rely too much on it.

If you liked this post on how to autofill in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.