Excel Zen Spreadsheet Blog

Excel Automatic Filling (Autofill) - The Basics

Excel is full of time saving features. One such feature is the ability to automatically fill cells with data. Excel is able to recognize data and patterns and fill cells in an intelligent way. This is a real boon when it comes to data entry as it can save you a lot of time.

Let’s look at the basic steps in automatic filling:

1. Enter data in one or two cells
2. Select the cells containing the data you’ve entered
3. Drag the fill handle down a column or across a row
4. Release the fill handle

Basically you need to enter sufficient for Excel to recognize what you want to fill and then tell Excel what range you want filled. Let’s make things a bit clearer by looking at an example. Suppose you want to fill a range with the months of the year. This is easy. Just enter “January” into a cell:

Select the cell containing January:

Move the cursor over the fill handle. (Note how the cursor changes to a plus sign “+” when the mouse is positioned over the fill handle.) Click and hold down the left mouse button:

Then drag the fill handle down:

Finally release the fill handle:

As you can see, Excel has filled cells with the months February through to December. In this example we clicked the left mouse button and dragged the fill handle. Excel will also let you click the right mouse button instead of the left mouse button. If you use the right mouse button then Excel will display a pop up menu that gives you more control over the way in which cells are filled. We’ll ignore this option for now for the sake of simplicity and discuss it in detail in a later post.

Excel can recognize patterns and replicate them when filling cells automatically. An example will help you see this. Let’s fill some cells with a series of months starting at March 2000 where the interval between successive elements in the series is three months. We can do this by entering “March 2000” in one cell and by entering “June 2000” directly below:

Select the cells containing “March 2000” and “June 2000”:

Move the cursor over the fill handle. Click and hold down the left mouse button:

Then drag the fill handle down:

Finally release the fill handle:

So far we’ve filled cells down columns. You can also fill cells across rows by dragging the fill handle horizontally. In this case you’ll need to make sure that you enter your initial data in the same row that you want to fill. So if you want to enter the series 5, 10, 15, 20, … in a row, you’ll need to enter 5 and 10 next to each other in the same row before you autofill. These pictures illustrate this:

Autofill is a really useful tool. But to make full use of it we need to understand the type of data and range of patterns that Excel can recognize and fill. That’s something we’ll look at in the next post.

Tags: Excel autofill, Excel automatic fill, fill dates, fill handle, fill series