Exceljet

Quick, clean, and to the point

The SEQUENCE function

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.

When columns is set to a number greater than 1, the SEQUENCE function will return a two-dimensional array.

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 in 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 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.

Dynamic Array Formulas are available in Office 365 only.

Related shortcuts

Author 
Dave Bruns