Author: Oscar Cronquist Article last updated on August 02, 2020

create number sequences using autofill

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, you need to rely on formulas.

The Autofill is also able to create date series and values containing both text and numbers, shown in columns F, H and J above.

1. Where is the Autofill button on the ribbon?

Autofill button on the ribbon

The Autofill button is located on tab "Home" on the ribbon. Click the "Fill" button and a pop-up menu appears.

Autofill button on the ribbon popup menu

The pop-up menu shows:

  • Down
  • Right
  • Up
  • Left
  • Acroos Worksheets...
  • Series...
  • Justify
  • Flash Fill

You can also create a number series using the dot in the lower right corner of the selected cell.

Autofill selected cell

The two first examples below demonstrate how to use the dot to create a series of numbers.

Back to top

1.1 How to create a number series from 1 to n

The following two examples show you how to create a number sequence using two different techniques. The Autofill feature allows you to quickly create a series of numbers.

Example 1

The animated image above shows how to create a number sequence from 1 to 5. You can create a much larger series

  1. Type 1 in cell B2.
  2. Press Enter.
  3. Right-click on the black dot and drag down as far as needed.
    A pop-up menu appears.
  4. Click "Fill series".

Back to top

Example 2

  1. Type 1 in cell A3 and 2 in cell A4
  2. Select A3 and A4
  3. Left click and hold on the black dot and drag down as far as needed.

Back to top

Example 3,

create number sequences using autofill

If you prefer a formula, try this one out:

=ROWS($A$1:A1)

The image above shows the formula entered in cell B2. Copy cell B2 and paste to cells below as far as needed.

The formula works as intended even if you insert rows above the cell B2.

Back to top

1.2 How to create a number series with every other number

create number sequences using autofill

The image above shows a list with every other number starting from 1 to n, in column D. To create this list follow these steps:

  1. Select cell D2.
  2. Type 1 and then press Enter.
  3. Select cell D3 if it is not already selected.
  4. Type 3 and press Enter on your keyboard.
  5. Select cell range D2:D3.
  6. Click and hold with left mouse button on the dot in the lower right corner of the selected cell range.
  7. Drag with mouse downwards as far as needed.
  8. Release left mouse button.

Back to top

1.3 How to create a series of dates

create number sequences using autofill

Column F in the image above shows a date series from 1/1/2020 to 1/5/2020. Here is how to quickly create it:

  1. Select cell F2.
  2. Type 1/1/2020 and then press Enter on your keyboard.
  3. Select cell F2 again.
  4. Click and hold with right mouse button on the dot located in the lower right corner of the select cell, in this case, cell F2.
  5. Drag with mouse downwards as far as needed.
  6. Release right mouse button.
  7. A pop-up menu appears.Autofill create a series of dates
  8. Click "Fill Series".

Back to top

1.4 How to create a series of dates based on a given period

create number sequences using autofill

Column H shows a date series based on every other week or 14 days.

  1. Select cell H2.
  2. Type 1/1/2020 or the start date you want to use, and then press Enter.
  3. Select cell H3 if it is not already selected.
  4. Type 1/15/2020 or the date you want to use, and press Enter on your keyboard.
    The Autofill tool will use the difference in days between the first date (H2) and the second date (H3) to create the remaining dates.
  5. Select cell range H2:H3.
  6. Click and hold with left mouse button on the dot located in the lower right corner of the selected cell range.
  7. Drag with mouse to cells below as far as needed.
  8. Release left mouse button.

Back to top

1.5 How to create a a series of values based on text and numbers

create number sequences using autofill

The Autofill tool allows you to use values containing both text and numbers as well. Column J shows this in the image above.

  1. Select cell J2.
  2. Type Item 1
  3. Select cell J2 again.
  4. Click and hold with left mouse button on the dot located in the lower right corner of the selected cell.
  5. Drag with mouse to cells below as far as needed.

Back to top

2. Create a repeating number sequence using a formula

In this example, I am going to create a repeating number sequence 1, 2, 3, 4.

This formula checks if the previous sequence number is 4, if true it restarts with value 1. If false it adds the previous sequence number with 1.

Select cell B3 and type 1. Then press Enter.

Formula in B4:

=IF(B3=4,1,B3+1)

Type above formula in cell B4. Press Enter.

Note, you don't need to enter all the formulas in cell range B2:B10. Only the number in cell B3 and the formula in cell B4.

Copy cell B4 and paste to cells below as far as needed. The formula uses relative cell references that change automatically when you copy the cell.

Back to top

Explaining formula in cell B4

The IF function allows you to control the outcome based on a condition or multiple conditions, in other words, it returns one value if the logical test is TRUE and another value if the logical test is FALSE.

It contains three parts, logical expression, value to return if the logical expression evaluates to true, and another value to return if the logical expression evaluates to false.

IF(logical_test, [value_if_true], [value_if_false])

Step 1 - Logical expression

B3=4

B3 is a relative cell reference, relative meaning it changes when the cell is copied and then pasted to another cell, in this case, the adjacent cell below.

For example, cell reference B3 changes to cell B4 when cell B4 is copied to cell B5.

Remember that this formula is in cell B4 so a cell reference to cell B3 is the adjacent cell above. B3 is 1.

B3=4

becomes

1=4

and returns boolean value FALSE. This value determines if the second argument or third argument will be evaluated next.

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

How to use the IF function

Step 2 - Next argument

IF(B3=4, 1, B3+1)

becomes

IF(FALSE, 1, B3+1)

The logical expression returns FALSE, this means that the third argument will now be calculated.

IF(FALSE, 1, B3+1)

becomes

IF(FALSE, 1, 1+1)

and returns 2 in cell B4.

Next cells

It is not until cell B7 something unexpected happens. The logical expression now evaluates to TRUE.

IF(B6=4, 1, B6+1)

becomes

IF(4=4, 1, B6+1)

becomes

IF(TRUE, 1, B6+1)

This will make the IF function calculate the second argument instead of the third as before.

IF(logical_test, [value_if_true], [value_if_false])

IF(TRUE, 1, B6+1)

returns 1. The series starts all over again beginning with 1.

Back to top

3. Create a number series and restart when a cell value equals a given condition

In this example, the number sequence restarts every time the adjacent cell value in column B is equal to "A".

Select cell C3 and type 1. Then press Enter.

Formula in C4:

=IF(B4="A",1,C3+1)

Copy cell C4 and paste to cells below as far as needed.

Back to top

Explaining formula in cell C4

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

Step 1 - Calculate logical expression

The logical expression is the first argument, in this case: B4="A". Cell reference B4 is a relative cell reference, it changes when the cell is copied to cells below. Note, you need to copy the cell not the formula for this to work.

The text string "A" is the condition, if cell B4 is equal to the condition it returns TRUE, otherwise FALSE. TRUE and FALSE are boolean values in Excel.

B4="A"

becomes

"D"="A"

and returns FALSE.

Step 2 - Return second argument if TRUE and third argument if FALSE.

The second argument is 1 and the third argument adds 1 to the number in cell C3.

IF(B4="A",1,C3+1)

becomes

IF(FALSE,1,C3+1)

becomes

IF(FALSE,1,1+1)

and returns 2 in cell C4.

Next cells

It is not until cell C7 things change in the formula.

IF(B7="A",1,C6+1)

becomes

IF("A"="A",1,C6+1)

becomes

IF(TRUE,1,C6+1)

and returns 1. The series now start all over with number 1.

Back to top

4. Create a number sequence to count records by year and month (sorted list)

This formula checks if the previous date has the same year and month as the current cell date. If true the previous sequence number is added by 1. If false the sequence starts all over again with 1.

The following formula will only work if the dates in column B are sorted from earliest to latest.

Formula in C4:

=IF(TEXT(B4, "M-YYYY")=TEXT(B3, "M-YYYY"), C3+1, 1)

Copy cell C4 and paste to cells below as far as necessary.

Back to top

Explaining formula in cell C4

Step 1 - Convert dates to month and year

TEXT(B4, "M-YYYY")

becomes

TEXT(B3, "M-YYYY")

becomes

Step 2 - Compare values

TEXT(B4, "M-YYYY")=TEXT(B3, "M-YYYY")

IF(logical_test, [value_if_true], [value_if_false])

Step 3 - If function returns one value if True and another value if False

IF(TEXT(B4, "M-YYYY")=TEXT(B3, "M-YYYY"), C3+1, 1)

5. Create a number sequence to count records based on year and month (unsorted list)

Array formula in  B32:

=SUMPRODUCT(--(TEXT(B3,"YYYY-M")=TEXT($B$3:B3,"YYYY-M")))

Copy cell C3 and paste to cells below as far as necessary.

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

How to use the SUMPRODUCT function

Back to top

6. Create a number sequence to count dates based on year

This formula works fine with both sorted and unsorted lists. It simply counts the current cell year in the previous years cell range.

Formula in B39:

=SUMPRODUCT(--(YEAR(B3)=YEAR($B$3:B3)))

Copy cell C3 and paste to cells below as far as necessary.

Back to top

7. Create a number sequence to count individual products

The COUNTIF function simply counts how many times the Item value has been displayed.

Formula in C3:

=COUNTIF($B$3:B3,B3)

The first argument has a cell range that expands when you copy the cell to cells below.

Copy cell C3 and paste to cells below as far as necessary.

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

Back to top

8. Create a number sequence to count prices within specific ranges

The formula in cell C9 creates a sequence depending on which range the price is in.

Formula in C9:

=SUMPRODUCT(--(MATCH(B9, $C$3:$C$6, 1)=MATCH($B$9:B9, $C$3:$C$6, 1)))

Copy cell C9 and paste to cells below as far as necessary.

Back to top

9. Create a number sequence to count records by individual products and years

Formula in D3:

=SUMPRODUCT(--(YEAR($B$3:B3)&(C3:$C$3)=YEAR(B3)&C3))

Copy cell D3 and paste to cells below as far as needed.

Back to top

Watch a video where I demonstrate the techniques described below

Back to top