Filling sequential data in Google Sheets is easy using the fill feature. But if you want to increase or decrease the numbers in your series or fill all cells in an array, consider the SEQUENCE function.
While the SEQUENCE function can perform much the same way as the fill feature, it’s the optional arguments that make it most useful. Want to fill both columns and rows in a range? Want to start at 525 and decrease each number by 18? How about doing both of these using dates? These are the types of things you can do with the SEQUENCE function in Google Sheets.
The syntax for 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.
Start: The number to begin 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.
Let’s take a look at how to use the SEQUENCE function with each of its arguments.
For a simple sequence of numbers filling 10 rows in one column using the defaults for the optional arguments, you’d use this formula:
For a sequence of numbers filling 10 rows and two columns using the defaults for the other two optional arguments, you would use this formula:
Notice the direction of the series. It fills to the right, then down to the left and to the right again, like reading a paragraph.
For a sequence filling the same number of rows and columns but starting with number 35, you would use this formula:
For a sequence filling the same number of rows and columns, starting with 35, and increasing each number by 10, you’d use this formula:
To decrease that same series of numbers by 10 instead of increasing it, you would use this formula:
You can also use cell references for the
step arguments if you like. For example, we’ll 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 increase each number by the value in cell B1 which is 25:
You can add the minus sign in front of the
step cell reference to decrease the numbers by the value in cell B1:
One disadvantage to the SEQUENCE function is that you cannot use it to fill text, like letters. But if you need to fill a series of dates, it works.
Here, we’ll fill 10 rows and two columns with dates starting with 1/1/2022. Notice the date argument is in quotes.
Now, we’ll increase the series of dates by 10 days:
If you need to fill text, formulas, months, or days, the fill feature is ideal. But if you need to fill a series of numbers for an out-of-the-ordinary situation, check out the SEQUENCE function in Google Sheets.