Excel’s SEQUENCE function can be used to generate either a 1-D list or a 2-D array of sequential numbers.
Example 1: Using SEQUENCE to generate sequential numbers in a column
Only the first cell (D1) actually has the formula in it. I didn’t have to drag the formula from D1 to D8 in order to populate the values in those cells. I simply entered the formula “=SEQUENCE(8)” once in cell D1 and the other cells were automatically populated with values.
If you click into another cell like D4, you’ll see the formula is displayed in gray (rather than black) in the top bar, indicating that the cell has been automatically filled by the formula.
If I manually change the value of D4, it will mess everything up because the SEQUENCE function wants to fill in this cell but can’t. As a result, you’ll get a #SPILL! error and the SEQUENCE function won’t work.
Example 2: Using SEQUENCE to generate sequential numbers that fill up a 2-D array of cells
In this case, we gave the SEQUENCE function two arguments instead of one. The first argument (3) is the number of rows and the second argument (5) is the number of columns. Importantly, we didn’t have to specify the total length of the sequence generated (15). Instead, we simply specified the size of a 2-D array (in this case 3×5) and then the SEQUENCE function automatically filled in the values starting in the top left and then working left-to-right and top-to-bottom (normal reading order).
Example 3: Using SEQUENCE to generate sequential numbers in a row
A row is just a 2-D column with a single row, so we can get a row of sequential numbers like this:
Example 4: Using SEQUENCE to generate a sequence starting with a specific value
Suppose we want to populate a 3×5 array of numbers but instead of starting with 1, this time we want to start with 4. We can do that by adding a third argument to the SEQUENCE function.
Example 5: Using SEQUENCE to generate a list of odd numbers
Suppose we want to create a 4×4 array with the first 16 odd numbers. We can do this by using a fourth argument for the SEQUENCE function which is the step size to use when incrementing numbers in the sequence. In our case, we want the step size to be 2 (so that, for example, we step from 1 to 3 instead of 1 to 2). Here’s how the final formula looks: