Excel SEQUENCE Function – Creating an Automatic Number Series

Excel SEQUENCE Function – Creating an Automatic Number Series. In this tutorial, you will learn how to create a number series in Excel using formulas. Additionally, we will show you how to automatically generate a series of Roman numerals and random integers – all using the SEQUENCE function. Manually sorting numbers in Excel is a thing of the past. In modern Excel, you can create a simple number series in a snap with the Auto Fill feature. Use the SEQUENCE function, designed specifically for this purpose.

The SEQUENCE Function in Excel

The SEQUENCE function in Excel is used to create an array of consecutive numbers like 1, 2, 3, etc.
This is a new dynamic array function introduced in Microsoft Excel 365. The result is an automatically expanding dynamic array into the specified rows and columns.

The function has the following syntax:
SEQUENCE(rows, [columns], [start], [step])
Specifically:
rows() – the number of rows to be filled.
columns() – the number of columns to be filled. If omitted, defaults to 1 column.
start() – the number to start the series. If omitted, defaults to 1.
step() – the increment for each successive value in the series. It can be positive or negative.
If positive, the next values will increase, creating an ascending series.
If negative, the next values will decrease, creating a descending series.
If omitted, this step will default to 1.

Basic Formula for Creating a Number Series in Excel

If you’re looking to fill a column with rows numbered starting at 1, you can use the simplest form of the SEQUENCE function in Excel:
To place numbers in a column:
SEQUENCE(n)
To place numbers in a row:
SEQUENCE(1, n)
Where n is the number of elements in the series.

For example, to fill a column with 10 ascending numbers, enter the following formula into the first cell (in our case, A2) and press Enter.
=SEQUENCE(10)
The result will automatically expand into other rows.

To create a horizontal series, set the rows argument to 1 (or omit it) and specify the number of columns, 8 in our case:
=SEQUENCE(1,8)

If you want to fill a range of cells with consecutive numbers, then specify both the rows and columns arguments. For example, to fill 5 rows and 3 columns, you would use this formula:

=SEQUENCE(5,3)

To start with a specific number, let’s say 100, provide that number in the 3rd argument:
=SEQUENCE(5,3,100)

To create a list of numbers with a specific increment, define the step in the 4th argument, 10 in our case:
=SEQUENCE(5,3,100,10)

SEQUENCE function – things to remember

To efficiently perform a sequence of numbers in Excel, remember the following four simple things:
The SEQUENCE function is only available with a Microsoft 365 subscription. In Excel 2019, Excel 2016, and earlier versions, it does not work because those versions do not support dynamic arrays.
If a range of consecutive numbers is the final result, Excel will automatically output all the numbers in a range called a spill range. So, make sure you have enough blank cells below and to the right of the cell where you enter the formula; otherwise, a #SPILL error will occur.
The result array can be one-dimensional or two-dimensional, depending on how you configure the row and column arguments.
Any optional argument not specified defaults to 1.

How to create a sequence of numbers in Excel – example formula

Although the basic SEQUENCE formula looks not very exciting, when combined with other functions, it will have a whole new level of usefulness.

Create a descending sequence in Excel

To create a sequential list in descending order, where each subsequent value is less than the previous value, provide a negative number for the step argument.
Example: To create a list of numbers starting from 10 and descending by 1, use the following formula:
=SEQUENCE(10, 1, 10, -1)

Create a Roman numeral sequence

Need a Roman numeral sequence for some tasks or just for fun? It’s easy! Build a regular SEQUENCE formula and wrap it in the ROMAN function. For example:
=ROMAN(SEQUENCE(B1, B2, B3, B4))
Where B1 is the row number, B2 is the column number, B3 is the starting number, and B4 is the step.

Create a random ascending or descending sequence of numbers
As you may know, in Excel 365, there is a special function to create random numbers, which is the RANDARRAY function. This function can do many useful things, but in our case, it cannot help. To create a random integer sequence in ascending or descending order, we will need the RANDBETWEEN function for the step argument of SEQUENCE.
Example: To create a sequence of random integers in ascending order in the number of rows and columns specified in B1 and B2 and starting from the integer in B3, the formula will be as follows:
=SEQUENCE(B1, B2, B3, RANDBETWEEN(1, 10))
Depending on whether you want a smaller or larger step, provide a lower or higher number for the second argument of RANDBETWEEN.

To create a sequence of numbers that repeats after a certain interval, we can use the MOD function in combination with the SEQUENCE function.