Excel Zen Spreadsheet Blog

Excel Automatic Filling (AutoFill) – Data and Patterns

In this post we continue exploring Excel’s autofill feature. Last time we looked at the basics of filling. We saw how to enter data in a few cells and then use the fill handle to extend that data to a larger range of cells. This time we take a closer look at the type of data Excel can recognize and fill.

Excel can recognize and autofill:

• Dates
• Times
• Numbers

The table below shows some examples of how Excel fills cells. Data has been entered in a single cell and then extended to other cells by using the autofill feature:

There are a couple of things to note here. Firstly, the left mouse button was depressed while the fill handle was dragged. It is possible to use the right mouse button instead and if you do so Excel gives you more options when filling cells. We will take a look at using the right mouse button in the next post. But for now we’ll keep it simple by considering what happens when you use the left mouse button.

The second point to note is that Excel has in some cases repeated the data that was entered and in other cases filled cells with a sequence of values. Numbers have been filled by repeating values whereas times and dates have been filled by creating sequences. If you want more control over the way in which Excel fills cells, you can enter data in two cells instead of just one. Basically you are giving Excel enough information to recgnize the pattern you want to use when filling cells. To see this, let’s take a look at what happens when data is entered in two cells and then filled using Excel’s autofill feature:

Here you can see that Excel has taken the difference between the two initial cells and added that difference to successive values to create a sequence of values. For example, the difference between 5 and 2 is 3. This has then been used as a “step value” to generate a sequence of values: 5+3=8, 8+3=11, 11+3=14 etc. Excel uses the same approach for dates and times. Take for example the two times 11:00 a.m. and 11:45 a.m. There is a difference of 45 minutes between these two times. If we add 45 minutes to 11:45 a.m. we get 12:30 p.m., and adding a further 45 minutes gives us 1:15 p.m. – this is exactly the sequence that Excel has produced.

There is one more thing that we need to talk about. Did you notice that Excel was able to increment numbers inside text? Take a look at the bottom three rows in the table above and you’ll see what I mean. This can be really handy if you need to label your spreadsheet data – adding text labels like “Observation 1”, “Observation 2”, “Observation 3”, … becomes effortless if you use Excel’s autofill feature.

Finally, let’s take a look at what happens when we enter values in three cells and then use autofill to fill a range of cells. Remember that we are dragging the fill handle while holding down the left mouse button. Here are the results of some test data sets:

The results may look a bit random but they aren’t. Excel uses the TREND worksheet function to calculate the best linear fit to the data (in the sense of least squares) and then uses the fitted line to fill the selected range. The chart below illustrates this for the data in the first row of the table above:

The examples that we’ve looked at are fairly limited. Autofill actually offers more flexibility and control than we’ve seen here. That’s something that we’ll explore in the next post.

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