The autofill function of Microsoft Excel is an incredibly useful tool, making it easy to repeat calculations hundreds (or even thousands) of times with very little effort.

There are a number of different ways in which the autofill tool can be used, and today we’re going to talk through some of those, and maybe also clear up any confusion you might be experiencing while trying to use autofills yourself.

What is Autofill?

Put simply, autofill is an Excel feature which will repeat formulas (and patterns), allowing the user to avoid repetitively typing the same thing over and over again.

While generally very good, autofill can sometimes behave in slightly unexpected ways, and so it’s good to try to understand how it works and what Excel is looking for when trying to perform an autofill.

First, though, let’s look at how an autofill is used.

Autofill by Clicking and Dragging

Let’s start with the simplest way to use autofill, which is by simply clicking and dragging.

Have a look at the below spreadsheet:

A screenshot of a spreadsheet I’ll be using to demonstrate the autofill function

What we’re going to do is add up the two numbers in each column (A and B), and insert the answer into the appropriate cells column C.

We’ll do this by typing the below formula into cell C2:

=A2+B2

A screenshot of a simple Excel formula being entered

Which provides the below result.

A screenshot showing the result of the above formula

As you can see, the bottom-right of cell C2 now has a small green box.

A screenshot showing the enlarged box at the bottom-right of cell C2

By clicking on the box, dragging down to cell C8 – and then releasing the mouse button – we can see that the formula has been repeated down the dragged cells.

A screenshot showing the result of clicking and dragging the formula down cells

Clicking any of the newly-filled cells will show that the formula has been repeated with relative values. In other words, Excel has seen the formula “=A2+B2” on row 2, and then changed this to “=A3+B3” on row 3, “=A4+B4” on row 4, and so on.

Note: It might be the case that you wanted to reference the same cell in every calculation. To find out how to do that, scroll down to the section titled “Using Static Values in an Autofill”.

Autofill by Double-Click

Sometimes it can be easier, rather than clicking and dragging, to just double-click the small box in the bottom-right corner of the cell (the one we discussed above).

By double-clicking, Excel will again repeat the formula down the column. However, you’ll notice it stops repeating at Cell C6.

A screenshot showing that our formula has stopped repeating

The autofill will only repeat a formula for as long as the data it is looking for is in a consistent position; once it encounters empty cells, it thinks it has completed its job. In this situation, I’ll just force the formula to repeat down to cell B7 by clicking and dragging on the corner-box down to C8.

Using Ctrl+E to Flash Fill

You can also use something called a Flash Fill in Excel. This tool attempts to extract text using recognizable patterns.

A very handy - and common - use of the Flash Fill is to extract forenames from email addresses. This can be handy when preparing a mail merge or other task where you require each recipients first name, but only have their email addresses to hand.

A screenshot of an Excel spreadsheet showing a list of email addresses

What I’ll do in this situation is manually type the desired information in Cell B2 (which is “Jack”, who is the first recipient on my list).

A screenshot of the same spreadsheet, this time with the first name entered

Now, with Cell B2 highlighted, I’m going to press CTRL+E. Excel will then attempt to autofill the remaining cells in the column by trying to follow my manually typed information:

A screenshot of the same spreadsheet with auto-filled information

Using Static Values in an Autofill

Another very handy tool when working with autofills is the ability to set static values. Let’s go back to our initial spreadsheet from the first example in this article:

A screenshot of a spreadsheet I’ll be using to demonstrate static values

Previously, we used autofill to copy the formula from C2 down the rest of the C column. This changed the cell references to be relative to each row:

Generally, this is going to be ok; but what if we wanted the second part of our formula to always refer to the cell in B2? In this case, we’d want:

So, how do we accomplish this? It’s actually quite simple. All we’d do is use a dollar ($) symbol before the part of our formula we wish to remain static. So I will enter the following in cell C2:

=A2+B$2

This provides the following result:

A screenshot of the same spreadsheet, this time using a static value

We can also put a dollar symbol in front of the letter if needed, too. However, we’d only do this when we were worried about that being changed by our autofill (something that will generally only happen when copying cells horizontally, rather than vertically). In that situation, we could just use:

=A2+$B$2

For more information on working with static cell values, have a read of this indeed.com article.

Summary

Hopefully this introduction to the autofill function has provided a decent little primer on some of the ways the tool can be used. As with all things Excel, it pays to experiment and go slowly when you’re learning about new functions! Have fun with them.