iSumsoft » Resources » Office » 2 Quick Ways to Autofill Date and Number in Excel

How to Autofill Date and Number in Excel Worksheet

Autofill feature in Excel allows to fill neighboring cells in any direction with data that follows a pattern or that is based on data in other cells. You can quickly fill cells in a range with built-in series for time, weekdays, months, years using fill handle or fill command.


The image below shows some of the series that are built-in to Excel, extended across the columns. The items in bold are the initial values we entered and the rest of the items in each row are the extended series values.

Type a value for series

Way 1: Fill a series date using the fill handle

With fill handle, you can quickly fill a series data like time, weekdays, months, years and numbers, like odd and even numbers, tens etc. Here's how;

Step 1: Select the first cell in the range that you want to fill.

Step 2: Type the starting value for the series, then type a value in the next cell to establish a pattern.

For Example: If you want the series Jan, Feb, Mar..., type in Jan.

If you want the series of years, type in 2011, 2012. In this case, you must select at least two cells with the values.

Type the startimg value for the series data

Step 3: Drag the fill handle Fill handle across the range that you want to fill.

Note: AutoFill works both vertically and horizontally.

Autofill data and number

Way 2: Fill a series number using Fill command.

Option 1: Fill a linear series using Fill command

With this option, it is very handy if you’re filling a large number of cells. Here's how:

Step 1: Select the first cell in the range that you want to fill, and type in the starting value for the series.

Step 2: On the Home tab, in the Editing group, click Fill, and then click Series.

Autofill date or number using Fill command

Step 3: On the Series dialog box, select whether you want the Series in Rows or Columns

Under Type, click Linear. Then enter the value into the Stop value box that you want as the last value in the series. Then click OK.

Fill in a large number of cells

In the following example, I put a 1 in the first cell of the first column set 100 as the last value in the series and the numbers 2 through 100 will be entered automatically into the next 99 cells.

Autofill a large number of data in cells

Option 2: Fill a Growth Series

To fill a growth series based on one number and a step value, here's how:

Step 1: Enter the first number in the first cell. select the cells range that you want to fill.

Select cells range

Step 2: On the Home tab, in the Editing group, click Fill, and then click Series.

Step 3: On the Series dialog box, select Growth under Type. In the Step value box, enter the value you want to multiply each number by to get the next value. In this example, I  want to multiply each number by 5.

Fill growth series data using Fill command

The growth series is filled in the selected cells, each subsequent number being five times the previous number.

The growth      series is filled in the selected cells

AutoFill option under type will creates a series that produces the same results as dragging the fill handle.

Autofill the same data