How to Use Sequence Function in Google Sheets

Published on

The Sequence function in Google Sheets is categorized under the Maths functions (Insert > Function > Maths).

The easiest way to generate an array of sequential number in Google Sheets is by using the Sequence function, not by using the Row function as below.

For example, you can replace the =ArrayFormula(row(A1:A10)) formula with a simple =SEQUENCE(10) formula.

By using Google Sheets Sequence function, you can generate a single as well as multiple column array of sequential numbers. Further, you have the option to provide a step value to control the numbers generated.

For example, if you provide #5 as the step value and start value as #0, the numbering will be like 0, 5, 10, 15, etc.

In addition to this, this Sequence function in Sheets can be used to generate sequential numbers vertically (single column) or horizontally (single row).

Normally we depend on the Row function (single column) or Column function (single row) together with the ArrayFormula for such outputs. Once you learn the Sequence function, you can skip using these combination formulas.

Let’s start with the Syntax and Arguments of the Sequence function in Google Sheets.

Syntax:

SEQUENCE(rows, columns, start, step)

Arguments:

rows – The number of rows to return.

columns – The number of columns to return.

start – By default the sequential number will start from 1. To start from a different number specify the start number.

step – By default, the number will increase by 1. Specify the number to change the increasing/decreasing.

Formula Examples to the Sequence Function in Google Sheets

Here I have a few formulas to make you understand the use of each argument in this function.

Generate Sequential Numbers in One Column Using the Syntax Sequence(rows):

=sequence(10)

This Sequence formula in Google Sheets will generate a single column array of numbers from 1 to 10. I mean a vertical output.

Sequence Formula - Row Output

Generate Sequential Numbers in One Row Using the Syntax Sequence(rows, columns):

To generate a single row sequential numbers use the function as below.

=sequence(1,10)

As you can see I have specified the number of ‘rows’ as 1 and ‘columns’ as 10.

Sequence Formula - Column Output

To generate sequential numbers in multiple columns and rows, follow the same syntax used in the just above example.

The formula =sequence(2,10) will return the numbers 1 to 10 as above in the first row and 11 to 20 in the second row.

In the below formula I am using all the arguments.

Multi-Column Sequentials:

=sequence(5,5,10,10)
Sequence Formula - Multi-Column Output

Negative Sequential Numbers:

Try the following formula in your Sheet and see the output.

=sequence(10,1,-1,-1)

How to Use the Sequence Function in Google Sheets to Generate Sequential Numbering in Reverse Order (Descending Order)?

To return the numbers in descending order you should use the same value in ‘rows’ and ‘start’ arguments. In addition to that, the ‘step’ value should be negative.

=sequence(10,1,10,-1)

This formula will return the numbers 10, 9, 8…1.

Dynamic Sequential Numbering in Google Sheets

To generate serial numbers in a column that up to the value in the last row in another column, we can use a Sequence + Match function combination.

First, see this normal formula that generates serial numbers 1-13 in a column.

=sequence(13)

Replace this formula with the below dynamic Sequence formula.

=ArrayFormula(sequence(MATCH(2,1/(B:B<>""),1)-1))
Dynamic Sequential Numbering in Google Sheets

In this dynamic Sequence formula, the Match formula returns the row number of the last value cell in column B.

I have explained that Match formula here – Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel. It’s related to Excel but equally works well in Docs Sheets.

I have subtracted the value 1 from the found number to adjust the header row which included in the count.

To get this numbering in descending order you can use this formula.

=ArrayFormula(sequence(MATCH(2,1/(B:B<>""),1)-1,1,MATCH(2,1/(B:B<>""),1)-1,-1))

It is equal to sorting the first dynamic formula in descending order.

=sort(ArrayFormula(sequence(MATCH(2,1/(B:B<>""),1)-1)),1,0)

Sequence Function to Generate an Array of Dates

Here is one more advanced tip. You can generate an array of dates using the Sequence function in Google Sheets.

To do that, you must make use of all the arguments in the function. Here is the generic formula to generate a column of sequential dates.

=sequence(row,column,start_date,step)

Formula to generate the dates from 01/01/2019 to 15/01/2019 in a column:

=sequence(15,1,date(2019,1,1),1)

The output will be in number format. Change that to date from Format > Number > Date.

Date in Sequence Function as Start Number

If the start date is in cell C1, refer to that using Indirect in Sequence.

=sequence(15,1,indirect("C1"),1)
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

8 COMMENTS

  1. Hi,

    How do I organize the filing of documents in Google Sheets using serial numbers?

    For instance:

    From [Control # 001-23] to [Control # 1000-23], how can I manage this in Google Sheets?

  2. Hi there!

    I would like to know how can I get Dynamic Sequential Numbering in Google Sheets.

    =ArrayFormula(sequence(MATCH(2,1/(not(B:B="")),1)-1))

    But I would like to add 0s in the sequence. Sample: 00001, 00002, etc.

    Thanks in advance!

  3. Is there a way in which sequence or any other formula would allow for more than one step?

    Let’s say there is a contract with a recurring date of the 15th of every month starting from 15 January 2023.

    Would there be a way that one can automatically populate a spreadsheet with the beginning date and ending date of the contract, where it will automatically list the date of every 15th of the month?

    • Hi, Martin,

      Use SEQUENCE in the months part of the EDATE function.

      Syntax: EDATE(start_date, [months])

      First, enter your start date, i.e., 15/01/2023, in cell A1.

      Formula:

      =ArrayFormula(edate(A1,sequence(12,1,0)))

  4. Would love to create a column that self populates an order number that has the formatting of:
    20-02-02.01
    20-02-02.02
    20-02-02.03

    Based on the day other columns have info entered, and sequencing up as multiple orders happen in some days.

    I could not figure it out.

    • Hi, Jim Garner,

      If the date is in B2:B, use the following formula in the second row in any column for example C2.

      =ArrayFormula(if(B2:B="",,(text(B2:B,"yy-mm-dd")&"."&text(COUNTIFS(B2:B,B2:B,ROW(A2:A),"<="&ROW(A2:A)),"00"))))

      Actually I have combined the dates in column range B2:B with the Running Count of the B2:B.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.