In this video, we'll introduce the SEQUENCE function.
One of the new functions that comes with the dynamic array version of Excel is SEQUENCE. The SEQUENCE function lets you generate numeric sequences, which can be used for dates, times, and more.
The SEQUENCE function takes four arguments. The first argument, rows controls how many rows SEQUENCE returns. This argument is required.
The second argument, columns, controls the number of columns returned by SEQUENCE. Columns is optional and defaults to 1.
The third argument is called start. This is the starting point for the numbers returned by SEQUENCE, and can be any valid number.
The last argument is step. Step is the interval used between each number. The step argument is optional and defaults to 1.
Let's look at how these arguments work.
On this worksheet, we have a place to enter rows, columns, start and step.
To begin with, I'll set up the SEQUENCE function to use rows only.
Now, I'll get an error until I enter a value, since the empty cell is interpreted as zero, and zero is not valid for rows.
I'll set rows to 12.
Since the other three arguments all default to one, SEQUENCE returns a spill range that includes 12 rows and one column.
These values start at 1, and are incremented by 1.
Next, I'll connect the other arguments to the cells on the worksheet.
Again, we see an error, because the blank cells are interpreted as zero, and zero is not valid for columns.
When I set columns to 1, the error disappears.
I'll go ahead and set start and step to 1 as well.
Now we can easily visualize the output from SEQUENCE. If I use 2 or 3 for columns, we get a two-dimensional array.
If I use 5 for columns, we now have a grid that contains 60 numbers. Notice the numbers increment across, then down.
Next, I'll adjust the start argument. Start can be any valid number. For example, I can use 10, 100, -100, or a decimal value.
Finally, let's look at the step argument, which controls the interval between numbers.
Like start, this can be any valid number.
I can use zero, -1, 100, or, a decimal number like .01.