Home > Formulas > Math Trig > SEQUENCE

Excel SEQUENCE Function Examples

Debra Dalgleish - Contextures

Instead of using the AutoFill feature to create a long list of numbers, save time with the Excel SEQUENCE function. Your list can be 1 row or column, or bigger, like the dates for a 4-week calendar. For Microsoft Excel 365 and Excel 2021 or later

sequence function list of numbers with cell reference

Excel SEQUENCE Function

Use the new 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.

sequence function creates 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, where dynamic arrays are supported.

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

Note: For the Start and Step arguments, you can use whole numbers or decimal numbers. Positive numbers, zero, and negative numbers are allowed.

Ex 1: List of Numbers

In the 3 examples below, the formulas return a list of sequential 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 array of sequential numbers, from 1 to 5, in 5 rows, down 1 column.

sequence function vertical list of numbers

B - Horizontal List of 5 Numbers

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

  1. rows argument is set as 1
  2. columns argument is set as 5
  3. 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. The sequential list spills into a range of cells with 1 row, across 5 columns.

sequence function horizontal list of numbers

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

sequence function list of numbers with cell reference

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 argument, and another 6 as the step argument.

Tip: For numbers in descending order, use a negative number as the Step value.

sequence function for table of numbers

Ex 3: Simple 4-Week Calendar

In the following example, the SEQUENCE formula 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.

sequence function creates 4-week calendar

Weekday Headings

The TEXT formula, in cell B3, is an old Excel function, not one of the new functions that spill. However, because the formula refers to a 7-cell range, the TEXT function acts like a dynamic array function, and 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

sequence function with MIN and MAX

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 following SEQUENCE function examples.

1) Weekly Declutter Schedule

How many items can you remove every day, to declutter your home or office? This declutter schedule workbook calculates a running total per week, and over multiple weeks.

You could use this technique for other plans too, such as minutes per day that you want to practise a new skill.

declutter schedule with SEQUENCE function

2) 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.

excel calendar from formulas

3) 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.

Extract Numbers from a String

Get the Sample Files

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.

Weekly Declutter Schedule -- Click here to download the Weekly Declutter Schedule sample file. Has examples for Excel 2019 or 365 (Schedule A), and for all versions of Excel (Schedule B). The zipped file is in xlsx format, and does not contain macros.

More Functions

Combine Text/Numbers

Date Functions

Spill Function Examples

 

 

VLOOKUP

 

Last updated: March 30, 2024 2:45 PM