This tutorial demonstrates how to use the SEQUENCE Function in Excel to generate a list of sequential numbers in an array, such as 1, 2, 3, 4, 5.

SEQUENCE Function Overview

The SEQUENCE Function generates a list of sequential values such as 1, 2, 3, 4, 5.

To use the SEQUENCE Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

SEQUENCE function Syntax and inputs:

=SEQUENCE(rows,[columns],[start],[step])

rows – The number of rows to return.

[columns] (optional)  – The number of columns to return.

[start] (optional) – The first number in the sequence.

[step] (optional) – The amount to increment each subsequent value in the array.

How to use the SEQUENCE Function

To generate a simple list of numbers

To generate a simple sequence of numbers that is 5 Rows tall by 1 Column wide, we enter the following formula in A2:

=SEQUENCE(5,1,1,1)

A simpler form of this formula is:

=SEQUENCE(5)

When the [columns], [start] and [step] arguments are omitted they default to 1.

To generate a simple sequence of numbers that is 5 Rows tall by 5 Columns wide, we enter the following formula in A2:

=SEQUENCE(5,5,1,1)

Use with other Functions

To generate a sequence of Dates, we enter the following formula in A2:

=SEQUENCE(5,1,C2,C5)

C2 is the Start Date whose value has been set to 1/1/2020.

C5 is the Number of Days the sequence increments by, it’s been set to 1.

We could use the DATE Function instead of referencing other cells. We enter the following formula in A2:

=DATE(2020,1,SEQUENCE(5))

To generate a list of month names using the DATE and TEXT Functions,  we enter the following formula in A2:

=TEXT(DATE(2020,SEQUENCE(5),1),"mmm")

To generate a sequence of times  using the TIME Function, we enter the following formula in A2:

=TIME(SEQUENCE(5),0,0)

 

Issues

#SPILL!

This error occurs when there is a value in the Spill Range i.e. the range where the SEQUENCE Function places its results.

To correct this error, clear the range that Excel highlights.

SEQUENCE Tips & Tricks

1. Ensure that the cells below the input cell are blank to avoid the Spill Error, learn more about the Spill Error ‘here’ — add link to Intro to DAFs.
2. SEQUENCE can be used with other Excel Functions such as the DATE Function and TIME Function to create more versatile formulas.

 

SEQUENCE in Google Sheets

The SEQUENCE Function works exactly the same in Google Sheets as in Excel: