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.
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.
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)
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))
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.
If the start date is in cell C1, refer to that using Indirect in Sequence.
=sequence(15,1,indirect("C1"),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?
In a blank column, enter this formula in the topmost cell:
=ArrayFormula("Control # "&TEXT(SEQUENCE(1000), "000")&"-"&23)I hope that helps.
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!
Hi, Jing,
Add the TEXT function to your formula.
=ArrayFormula(text(sequence(MATCH(2,1/(not(B:B="")),1)-1),"0000"))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)))Would love to create a column that self populates an order number that has the formatting of:
20-02-02.0120-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.