Home > Formulas > Math Trig > SEQUENCE

Excel SEQUENCE Function Examples

Use the Excel SEQUENCE function to quickly create a list of numbers on a worksheet. The list can be a single row or column, or in multiple rows and columns, like the dates for a 4-week calendar. The examples and tips below will show you how to get started, and get the most out of this powerful function.

SEQUENCE is available in Excel 365 or Excel 2021 or later

Excel SEQUENCE Function

Use the Excel SEQUENCE function to quickly create a list of numbers on a worksheet. The list can be a single row or column, or in multiple rows and columns, like the dates for a 4-week calendar.

With SEQUENCE, you can set a specific starting number for your sequence, and the interval between numbers. For example:

• Start with 1, and increase by 3s: 1,4,7,10.
• Start with 100, and increase by 100s: 100,200,300,400,500

The examples and tips below will show you how to get started, and how to get the most out of this powerful function.

SEQUENCE is available in Excel 365 or Excel 2021 or later

SEQUENCE Syntax

The SEQUENCE function has the following syntax for its arguments, with one required argument, and 3 optional arguments:

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

The 4 arguments are:

• rows - required - number of rows to use in formula result
• columns - optional - number of columns to use in formula result. Default is 1
• start - optional - first number in sequence. Default is 1
• step - optional - amount to increment each subsequent value. Default is 1

Ex 1: List of Numbers

In the 3 examples below, the formulas return a sequence of numbers, starting with 1, and ending with 5

A - Vertical List of 5 Numbers

With just one argument in the SEQUENCE function, you can create a simple list of numbers.

• first argument, rows, is set as 5
• remaining 3 arguments are omitted, so the default of 1 is used

=SEQUENCE(5)

The formula result is a vertical list of numbers, from 1 to 5, in 5 rows, down 1 column.

B - Horizontal List of 5 Numbers

With two arguments in the SEQUENCE function, you can create a horizontal list of numbers, across multiple columns.

• first argument, rows, is set as 1
• second argument, columns, is set as 5
• remaining 2 arguments are omitted, so the default of 1 is used

=SEQUENCE(1, 5)

The formula result is a horizontal list of numbers, from 1 to 5, in 1 row, across 5 columns.

C - Use Cell References

For the arguments in the SEQUENCE function, you can use cell references, instead of typing the numbers into the formula.

In the screen shot below, a number is typed in cell D6, and the two SEQUENCE formulas refer to that cell.

Vertical List
    =SEQUENCE(D6)

Horizontal List
    =SEQUENCE(1, D6)

When a different number is entered in cell D6, both number lists update automatically

Ex 2: Table of Numbers

With the following SEQUENCE formula, the result is a table of numbers, with 3 rows and 5 columns. The starting number is entered in cell B2, and the step value is entered in cell C2.

Here is the formula in cell B4, to create the table of numbers:

=SEQUENCE(4,5,B2,C2)

For example, if you want to practise counting by sixes, type a 6 as the start number, and another 6 as the step number.

Ex 3: Simple 4-Week Calendar

With this SEQUENCE formula, the result is a 4-week calendar, starting with the current date, which is created by the TODAY function.

Here is the calendar formula in cell B4, to create the calendar dates:

=SEQUENCE(4,7,TODAY())

I've formatted the calendar cells to show the day number, with a 3-letter month code, so it's easier to read.

Note: The calendar, and the weekday headings, will update each day, when you open the workbook, to start with the current date.

Weekday Headings

The TEXT formula, in cell B3, is an old Excel function, not one of the new "spill" functions. However, because the formula refers to a 7-cell range, the TEXT function automatically spills across all 7 columns, to show the weekday names, in a 3-letter format (ddd).

• =TEXT(B4:H4,"ddd")

=SEQUENCE(7, 1, 15, 3)

SEQUENCE with MIN and MAX

Here's another example of how you can use the SEQUENCE function with other Excel functions.

In the screen shot below, I've added two formulas at the top of the calendar worksheet, in cells F1 and H1. These formulas show the calendar's start date and end date, formatted as d-mmm

MIN and MAX Dates

Here is the start date formula, in cell F1, which combines the Excel MIN function with SEQUENCE, to return the earliest date in the 4-week calendar:

=MIN(SEQUENCE(4,7,TODAY()))

And here is the end date formula, in cell FH, which combines the Excel MAX function with SEQUENCE, to return the latest date in the 4-week calendar:

=MAX(SEQUENCE(4,7,TODAY()))

More SEQUENCE Examples

If you want to go beyond the basics, take a look at the three following SEQUENCE function examples.

1) Printable Excel Calendar

See how you can make a printable Excel calendar, based on a few short formulas, with SEQUENCE creating the dates. Change the year or month number, to update the calendar, with that month's holidays highlighted. Step-by-step video and written steps.

There are written steps, a step-by-step video, and a completed Excel workbook that you can download.

2) Extract Numbers from a String

On the TEXTJOIN function page, I used SEQUENCE in a formula that extracts just the numbers, from a mixed string of text and numbers. (see Example 6 on that page).

Here is the formula in cell B2, which was copied down to cell B6:

• =IFERROR(--TEXTJOIN("",TRUE, IFERROR(--MID(A2, SEQUENCE(LEN(A2)),1),"")),"")

There are full details for this formula on the TEXTJOIN function page.

3) Compare Cell Contents

And here's one more example, which shows how SEQUENCE can make formulas shorter, and calculate faster, compared to older functions.

In the list shown below, addresses in column A are compared to addresses in column B. What percentage do the cell contents match, starting from the first character?

Here is the formula in cell D2 (Match Len), which calculates how many characters are in the matching string.

=SUMPRODUCT(--(LEFT(A2, SEQUENCE(C2))
=LEFT(B2,SEQUENCE(C2))))

In older versions of Excel, we had to use a longer formula, using ROW and INDIRECT functions to create a sequence of numbers. INDIRECT is a volatile function, which can slow down workbook calculations.

=SUMPRODUCT( –(LEFT(A2, ROW(INDIRECT("A1:A" & C2)))
=LEFT(B2, ROW(INDIRECT("A1:A" & C2)))))

To see the full details on this example, and to get the sample workbook, go to the Compare Cell Values in Excel - Troubleshoot page. this is in the Partially Compare Two Cells section.

Get the Sample File

SEQUENCE Get Started: Click here to download the sample file for the SEQUENCE examples. For Excel 2019 or 365. The zipped file is in xlsx format, and does not contain macros.

More Functions

Combine Text/Numbers

Date Functions

Spill Function Examples