Filling sequential data in Google Sheets is easy with the fill feature. But if you want to increase or decrease the numbers in your series or fill all the cells in an array, consider the SEQUENCE function.
Although the SEQUENCE function can work in the same way as the padding function, it is the optional arguments that make it more useful. Do you want to fill columns and rows in a range? Do you want to start at 525 and decrease each number by 18? How about doing both using dates? These are the kinds of things you can do with the SEQUENCE function in Google Sheets.
About the SEQUENCE function
The syntax of the function is
SEQUENCE(rows, columns, start, step) where only the first argument is required.
rows: The number of rows you want to fill.
columns: The number of columns you want to fill. If omitted, the formula assumes one column.
beginning: The number to start the sequence. If omitted, the formula starts at 1.
Step: The amount to increase or decrease each number in the series. If omitted, the formula increases each number by 1.
Use the SEQUENCE function
Let’s see how to use the SEQUENCE function with each of its arguments.
For a simple sequence of numbers that fills 10 rows in a column using the default values for the optional arguments, you would use this formula:
For a sequence of numbers that fills 10 rows and two columns using the default values for the other two optional arguments, you would use this formula:
Note the direction of the string. It fills to the right, then down to the left, and then to the right again, as if you were reading a paragraph.
For a sequence that fills the same number of rows and columns but starts with the number 35, you would use this formula:
For a sequence that fills the same number of rows and columns, starting with 35 and increasing each number by 10, you would use this formula:
To decrease that same series of numbers by 10 instead of increasing it, you would use this formula:
SEQUENCE with cell references
You can also use cell references for the
step arguments if you want. For example, we will fill 10 rows and two columns starting with the value in cell B2 which is 5:
And here, we’ll use those same arguments and increment each number by the value of cell B1, which is 25:
You can add the minus sign in front of the
step cell reference to decrement the numbers by the value in cell B1:
SEQUENCE with dates
One disadvantage of the SEQUENCE function is that you cannot use it to fill text, such as letters. But if you need to fill in a series of dates, it works.
RELATED: How to change the default date format in Google Sheets
Here, we will fill 10 rows and two columns with dates starting with 1/1/2022. Notice that the date argument is enclosed in quotes.
Now, we will increase the date series by 10 days:
If you need to fill in text, formulas, months or days, the fill function is ideal. But if you need to fill in a series of numbers for an unusual situation, check out the SEQUENCE function in Google Sheets.