How to use Autofill in Excel

Posted on May 24, 2009 at 5:26 am

If you have a series of data that you want to enter into Excel, it is best to let AutoFill handle it. With AutoFill, your serial data gets automatically populated – you will only need to enter the first value on the list and the software will populate the rest for you.

To use this feature, start by entering the initial values of the row. In the example below, I entered a date for column A and a numeric value for column B. You can use Excel to autofill dates, numbers, and characters.

Select the initial values and drag the black corner at the lower right portion of the selected cells. This is called the Fill handle. Drag this to the 10th row of the sheet.

The output will be a progression of dates from 5/1/2009 to 5/10/2009 and numbers from 1 to 10.

On column A, the values are iterated by 1 day. Column B on the other hand is by 1. Columns 2 to 10 are automatically filled by serial values. This is much easier than typing manually isn’t it? Excel, by default, adds one unit to a series. This works for data types like dates and numbers.

Now what if you need to iterate it using another value? For example, what if we want to iterate by month and by number 2? The trick here is to “train” Excel by feeding it 2 initial rows so it could determine the amount of iteration used. See the example below:

Column A is iterated by 1 month while Column B is iterated by 2. If you select both rows and drag the Fill handle downwards you will see a series like below: