To create or make a list of sequential numbers in excel, Microsoft has come up with a new formula in its Excel 365 version – The SEQUENCE Excel function.
In this tutorial, we would learn how to create and generate a series of simple numbers in excel using the SEQUENCE formula. We would also learn to create a list of roman numbers using the combination of the SEQUENCE and ROMAN excel function.
- When to Use Excel SEQUENCE Function
- Syntax and Arguments
- Examples of SEQUENCE Function [With Explanation]
- Ex. 1 # Generate Serial Number Starting with 1 in Excel (Vertically)
- Ex. 2 # Generate Serial Number Starting with 1 in Excel (Horizontally)
- Ex. 3 # Generate Sequence of Number Starting With Other Than 1
- Ex. 4 # Generate Sequential Numbers with Intervals Between Two Numbers
- Ex. 5 # Create Serial Number in Descending (Decreasing) Numbers
- Do Not Miss These Points
Here we go 😎
When to Use Excel SEQUENCE Function
Excel SEQUENCE formula is a part of math/trig function group. This function is very useful to quickly generate a range of sequential numbers in excel with or without intervals.
Additional benefits of using the SEQUENCE excel formula:
- The SEQUENCE function in excel allows you to specify the sequence or series start number.
- You can also specify the number of intervals or gap between each number in a sequence.
- You can create sequence of numbers in rows (from up to down), or columns (from left to right), or both rows and columns.
Syntax and Arguments
=SEQUENCE(rows, [columns], [start], [step])
The SEQUENCE formula has one mandatory and three optional input arguments-
- rows – In this argument, mention the number of rows to fill with the sequence.
- [columns] – Optional argument. In this argument, enter the number of columns to fill with the number sequence. By default, it takes 1.
- [start] – Optional argument. In this argument, specify the starting number of a sequence. If kept blank, then the series would start with 1.
- [step] – Optional argument. In this argument, specify the interval/gap between two numbers in sequence of numbers.
Let us now see some examples to understand how the SEQUENCE formula works in excel.
Examples of SEQUENCE Function [With Explanation]
Ex. 1 # Generate Serial Number Starting with 1 in Excel (Vertically)
To create serial numbers starting with 1 in an excel column using a formula, simply use the following formula:
As a result excel generates serial number starting from 1 to 8 in a column from up to down, as shown in the image below:
By default, excel creates vertical sequence of numbers (as learned above)
Note that the SEQUENCE formula is SPILL excel function which means that when you type the formula in a cell, it spills the values in other cells automatically.
In the above example, we saw how to create a one-dimensional sequential sequential numbers.
Now, let us look learn to create two-dimensional sequential sequential numbers.
To generate serial numbers in the form of a table (i.e. range of cells), specify the number of rows and columns as the formula input argument. To fill range of cells with sequence of numbers, lets say in 4 rows and 2 columns, use below formula:
Ex. 2 # Generate Serial Number Starting with 1 in Excel (Horizontally)
To create and generate a horizontal sequence of numbers (from left to right) starting from 1, simply use the rows input argument as 1 and columns input argument as 8.
Ex. 3 # Generate Sequence of Number Starting With Other Than 1
If you want to generate a list of sequential numbers starting with a nth number, simply specify the starting number in the third argument.
For example, to start serial number with 100 in excel, use below formula:
As a result, excel would return the below output.
Ex. 4 # Generate Sequential Numbers with Intervals Between Two Numbers
To generate sequential numbers with an increment of n numbers between two numbers, use the fourth argument.
Let’s say, you want to generate a sequence of seven numbers starting with 50 having interval of 5 in between each number.
In order to achieve this, enter the interval value 5 in the fourth argument of the SEQUENCE function in excel.
Ex. 5 # Create Serial Number in Descending (Decreasing) Numbers
To generate serial number in a decreasing or descending order, enter a negative number in the [step] argument.
For example to generate reverse serial numbers from 8 to 1, use following formula:
As a result, excel return a number sequence in reverse order.
Do Not Miss These Points
- SEQUENCE function may be missing in your excel. In that case, firstly check you excel version. Is it Excel 365 version?
The SEQUENCE formula in excel is only available for those users who have Microsoft Office 365 subscription with them. Get is here.
- Older version excel users (Excel 2019, Excel 2016, Excel 2013 and Excel 2010) can still generate serial number using the ROW and COLUMN excel functions.
- If you omit the optional arguments ([column], [start], [step]), it always default to 1.
- To generate the serial number in Roman format, put the SEQUENCE formula inside the ROMAN excel function, like this:
Thank You 🙂