Excel has a great built-in versatile tool for creating number sequences. In some situations, however, you need to rely on formulas. Read on to find many examples.

Watch a video where I demonstrate the techniques shown below

Create number sequences (Autofill)

The following two examples show you how to create a number sequence using two different techniques.

Example 1

  1. Type 1 in cell B2 and then press Enter
  2. Right click on the black dot and drag down as far as needed.
  3. Click "Fill series"

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.

Create a repeating number sequence

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 previous sequence number with 1.

Select cell B3 and type 1. Then press Enter.

Formula in B4:

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

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

IF function explained

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

Create a number sequence and restart when a cell value equals a criterion

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.

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 are sorted.

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.

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 Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

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.

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.

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

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.

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.