The SEQUENCE function is a useful function in Google Sheets. Its a powerful way to generate numbered lists.=SEQUENCE(rows, columns, start, step)
As arguments for the SEQUENCE function, you specify 1) the number of rows, 2) the number of columns, 3) a start value, and 4) a step size.
Arguments 2, 3, and 4 are optional. However, if you want to set them you need to include the previous ones (e.g. if you want to set a step size in argument 4, then you need to set 1, 2, and 3 as well).
Keep this order in mind as you look through the examples below and youll soon understand how the function works.
1. Ascending list of numbers=SEQUENCE(5)
2. Horizontal list of numbers
Set the row count to 1 and the column count to however many numbers you want e.g. 5:=SEQUENCE(1,5)
3. Two-dimensional array of numbers
Set both row and number values:=SEQUENCE(5,5)
4. Startfrom a specific value
Set the third argument to the value you want to start from e.g. 100:=SEQUENCE(5,1,100)
5. Usea custom step
Set the fourth argument to the size of the step you want to use, e.g. 10:=SEQUENCE(5,1,1,10)
6. Descending numbers
Set the fourth argument to -1 to count down:=SEQUENCE(5,1,5,-1)
7. Negative numbers
Set the start value to a negative number and/or count down with negative step:=SEQUENCE(5,1,-1,-1)
Dates are stored as numbers in spreadsheets, so you can use them inside the SEQUENCE function. You need toformat the column as dates:=SEQUENCE(5,1,TODAY(),1)
9. Decimal numbers
Unfortunately you cant set decimal counts directly inside the SEQUENCE function, so you have to combine with an Array Formula e.g.=ArrayFormula( SEQUENCE(5,1,10,1) / 10 )
10. Constant numbers
Youre free to set the step value to 0 if you want an array of constant numbers:
11. Monthly sequences
Start with this formula in cell A1, which gives the numbers 1 to 12 in a column:=SEQUENCE(12)
In the adjacent column,use this DATE function to create the first day of each month (formula needs to be copied down all 12 rows):=DATE(2021,A1,1)
This can be turned into an Array Formula in the adjacent column, so that a single formula, in cell C1, outputs all 12 dates:=ArrayFormula(DATE(2021,A1:A12,1))
Finally, the original SEQUENCE formula can be nested in place of the range reference, using this formula in cell D1:=ArrayFormula(DATE(2021,SEQUENCE(12),1))
This single formula gives the output:
Its an elegant way to create a monthly list. Its not dependent on any other input cells either (columns A, B, C are working columns in this example).
With this formula, you can easily change all the dates, e.g.to 2022.
Buildingin steps like this a great example of theOnion Method, which I advocate for complex formulas.
12. Text and Emoji sequences
You can use a clever trick to set the SEQUENCE output to a blank string using the TEXT function. Then you can append on a text value or an emoji or whatever string you want to create a text list.
For example, this repeats the name Ben Collins one hundred times in a column:=ArrayFormula(TEXT(SEQUENCE(100,1,1,1),"")&"Ben Collins")
And, by using the CHAR function, you can also make emoji lists. For example, heres a 10 by 10 grid of tacos:=ArrayFormula(TEXT(SEQUENCE(10,10,1,1),"")&CHAR(127790))
13. Lists Of Grouped Numbers
Suppose were organizing an event and we want to group our 20 participants into groups of 5.
Start with the standard SEQUENCE function to output a numbered list from 1 to 20:=SEQUENCE(20)
Next, divide by 5:=SEQUENCE(20)/5
This gives a single output, 0.2, so we need to wrap it with an array formula to get the full column output:=ArrayFormula(SEQUENCE(20)/5)
Finally, we add ROUNDUP to create the groups shown in the image above:=ArrayFormula(ROUNDUP(SEQUENCE(20)/5))
14. Repeating Sequence
To create a repeating sequence 1,2,3,1,2,3,1,2,3,etc. use the SEQUENCE function in combination with the MOD function:=ArrayFormula(MOD(SEQUENCE(100,1,0),3)+1)
Have you got any examples of using the SEQUENCE function?