by In this tutorial, you will learn how to create a number sequence in Excel with formulas. Additionally, we'll show you how to auto generate a series of Roman numbers and random integers - all by using a new dynamic array SEQUENCE function.
The times when you had to put numbers in sequence in Excel manually are long gone. In modern Excel, you can make a simple number series in a flash with the Auto Fill feature. If you have a more specific task in mind, then use the SEQUENCE function, which is specially designed for this purpose.
Excel SEQUENCE function
The SEQUENCE function in Excel is used to generate an array of sequential numbers such as 1, 2, 3, etc.
It is a new dynamic array function introduced in Microsoft Excel 365. The result is a dynamic array that spills into the specified number of rows and columns automatically.
The function has the following syntax:
Where:
Rows (optional) - the number of rows to fill.
Columns (optional) - the number of columns to fill. If omitted, defaults to 1 column.
Start (optional) - the starting number in the sequence. If omitted, defaults to 1.
Step (optional) - the increment for each subsequent value in the sequence. It can be positive or negative.
- If positive, subsequent values increase, creating an ascending sequence.
- If negative, subsequent values decrease, producing a descending sequence.
- If omitted, the step defaults to 1.
The SEQUENCE function is only supported in Excel for Microsoft 365, Excel 2021, and Excel for the web.
Basic formula to create a number sequence in Excel
If you are looking to populate a column of rows with sequential numbers starting at 1, you can use the Excel SEQUENCE function in its simplest form:
To put numbers in a column:
To place numbers in a row:
Where n is the number of elements in the sequence.
For example, to populate a column with 10 incremental numbers, type the below formula in the first cell (A2 in our case) and press the Enter key:
=SEQUENCE(10)
The results will spill in the other rows automatically.
To make a horizontal sequence, set the rows argument to 1 (or omit it) and define the number of columns, 8 in our case:
=SEQUENCE(1,8)
If you'd like to fill a range of cells with sequential numbers, then define both the rows and columns arguments. For instance, to populate 5 rows and 3 columns, you'd use this formula:
=SEQUENCE(5,3)
To start with a specific number, say 100, supply that number in the 3rd argument:
=SEQUENCE(5,3,100)
To generate a list of numbers with a specific increment step, define the step in the 4th argument, 10 in our case:
=SEQUENCE(5,3,100,10)
Translated into plain English, our complete formula reads as follows:
SEQUENCE function - things to remember
To efficiently do a sequence of numbers in Excel, please remember these 4 simple facts:
- The SEQUENCE function is only available with Microsoft 365 subscriptions and Excel 2021. In Excel 2019, Excel 2016 and earlier versions, it does not work since those versions do not support dynamic arrays.
- If the array of sequential numbers is the final result, Excel outputs all the numbers automatically in a so-called spill range. So, be sure you have enough empty cells down and to the right of the cell where you enter the formula, otherwise a #SPILL error will occur.
- The resulting array can be one-dimensional or two-dimensional, depending on how you configure the rows and columns arguments.
- Any optional argument that is not set defaults to 1.
How to create a number sequence in Excel - formula examples
Though the basic SEQUENCE formula does not look very exciting, when combined with other functions, it takes on a whole new level of usefulness.
Make a decreasing (descending) sequence in Excel
To generate a descending sequential series, such that each subsequent value is less than the preceding one, supply a negative number for the step argument.
For example, to create a list of numbers starting at 10 and decreasing by 1, use this formula:
=SEQUENCE(10, 1, 10, -1)
Force a two-dimensional sequence to move vertically top to bottom
When populating a range of cells with sequential numbers, by default, the series always goes horizontally across the first row and then down to the next row, just like reading a book from left to right. To get it to propagate vertically, i.e. top to bottom across the first column and then right to the next column, nest SEQUENCE in the TRANSPOSE function. Please note that TRANSPOSE swaps rows and columns, so you should specify them in the reverse order:
For example, to fill 5 rows and 3 columns with sequential numbers starting at 100 and incremented by 10, the formula takes this form:
=TRANSPOSE(SEQUENCE(3, 5, 100, 10))
To better understand the approach, please have a look at the screenshot below. Here, we input all the parameters in separate cells (E1:E4) and create 2 sequences with the below formulas. Please pay attention rows and columns are supplied in different order!
Sequence that moves vertically top to bottom (row-wise):
=TRANSPOSE(SEQUENCE(E2, E1, E3, E4))
Regular sequence that moves horizontally left to right (column-wise):
=SEQUENCE(E1, E2, E3, E4)
Create a sequence of Roman numbers
Need a Roman number sequence for some task, or just for fun? That's easy! Build a regular SEQUENCE formula and warp it in the ROMAN function. For example:
=ROMAN(SEQUENCE(B1, B2, B3, B4))
Where B1 is the number of rows, B2 is the number of columns, B3 is the start number and B4 is the step.
Generate an increasing or decreasing sequence of random numbers
As you probably know, in new Excel there is a special function for generating random numbers, RANDARRAY, which we discussed a few articles ago. This function can do a lot of useful things, but in our case it cannot help. To generate either an ascending or descending series of random whole numbers, we'll be needing the good old RANDBETWEEN function for the step argument of SEQUENCE.
For example, to create a series of increasing random numbers that spills in as many rows and columns as specified in B1 and B2, respectively, and start at the integer in B3, the formula goes as follows:
=SEQUENCE(B1, B2, B3, RANDBETWEEN(1, 10))
Depending on whether you want a smaller or bigger step, supply a lower or higher number for the second argument of RANDBETWEEN.
To make a sequence of decreasing random numbers, the step should be negative, so you put the minus sign before the RANDBETWEEN function:
=SEQUENCE(B1, B2, B3, -RANDBETWEEN(1, 10))
Note. Because the Excel RANDBETWEEN function is volatile, it will generate new random values with every change in your worksheet. As the result, your sequence of random numbers will be continuously changing. To prevent this from happening, you can use Excel's Paste Special > Values feature to replace formulas with values.
Excel SEQUENCE function missing
Like any other dynamic array function, SEQUENCE is only available in Excel for Microsoft 365 and Excel 2021 that support dynamic arrays. You won't find it in pre-dynamic Excel 2019, Excel 2016, and lower.
That's how to create sequence in Excel with formulas. I hope the examples were both useful and fun. Anyway, thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel SEQUENCE formula examples (.xlsx file)
437 comments
Hello,
I want to create a reference number formula in an excel cell in a payment voucher such that when I save one payment voucher and I close and open the payment voucher again, It will increment itself.
Thank you.
Hi! Unfortunately, without seeing your data, it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.
Hello
I'm attempting to link two different spreadsheets, one being a view/ work-log, the other is an entry-log. The work-logs only purpose is to calculate the remaining dollar amount and percentages. The entry-log is an ongoing form that totals around 50 individual sheets, which means every new entry will differ. The entry-log will only use 7-12 cells that reflect on the work-log in sequential order. The problem I'm having is how to add a traspose fx that will work in sequence with the two forms to prevent me from having to link them manually for each sheet?
Is it possible to add a sequence with this formula below without removing the transpose fx?
=TRANSPOSE('[Weatherization - Joshen RFP.xlsx]JOSHEN '!$C$14:$C$17)
Hello! It is very difficult to understand a formula that contains unique references to your data, which I don't have. Unfortunately, I can't understand what you want to do.
hi,
I have three columns : employee, salary (yearly), startdate. I want to generate a provisional monthly cashflow table for the next 3 years with three columns : employee, payment (monthly), paymentdate.
I know how to this using SEQUENCE for one employee but is there a way to generate it at once for several employees ? In other words, this would be the same as generating sequence lines one below the previous.
Thanks
Hi! You can create only one sequence using the SEQUENCE formula.
Hi,
I have CSV data that has been loaded into Excel using the common method under the Data / From Text menu, into a separate worksheet labelled "Raw Data". I want to paste this data into another worksheet from a start position identified as "1". The simple formula used to achieve the "1" starting position is =IF(CE$4=BJ695,1,""), where CE$4 is time in DD/MM/YYYY hh:mm:ss format and BJ695 is one of a series of cells with the same time format. The time is in sequential order in column BJ. The formula identified places a "1" as the starting position in column "CE". In the adjacent column "CD" the data from the worksheet "Raw Data" should start. I have tried Index Match formulae to no avail and am losing my mind over this. Is there a simple way of completing this task without introducing many more helping columns, as the worksheet is already large? I would greatly appreciate any help provided.
Many thanks... Damian.
Hi! Dates that you load to Excel from a CSV file, are usually not written as dates, but as text. Check this and use these guidelines: How to convert text to date in Excel. I hope it’ll be helpful.
Can I use the sequence function if I want to repeat number 12 times then incrementing 1 until I reached 24 it goes back to number 1? Thanks
I don't think this can be done with a sequence function. Write down 24 numbers and copy them as many times as necessary.
Hi , I want to apply a function on a generated sequence. Say there are 5 numbers {1,2,3,4,5} , I want harmonic sum of each the numbers in the sequence and then I want cumulative sum of the results. How will it be possible ?
Hi! Please clarify your specific problem or provide additional information to understand what you need. Maybe this article will be helpful: How to do a running total in Excel (Cumulative Sum formula).
Hi ,
Seek your guidance how shall i proceed built the sequence formula for below scenario in excel template
1.scenario 1
13/9/ABC/01
13/9/ABC/02
13/9/ABC/03
13/9/ABCa/04
13/9/ABC/05
13/9 > referring to DD/MM base on today/weekday case ID receive
ABC - defaulted
01 - refer to sequence number for each entry of the row
2. Scenario 2 sequence format
202309120001
202309120002
202309120003
202309120004
"20230912" - referring to current request date receive for the data entry
"0001", 002,003 - referring to sequence number of request received for the day
if the subsequent date no request, then will no case ID generate
Assume case receive on 20230915, then the case ID number will be 2020309150001.....follow by 202309150002...etc
Thank you.
Hi! Use the TEXT function to write dates and numbers in the desired format.
=TEXT(A1,"mm.dd")&"."&"ABC/"&TEXT(SEQUENCE(10,1,1,1),"00")
thank you so much on your useful tips.
The serial number working perfectly for Date 9 Oct 2023. However, when is new date eg: 10 Oct 2023, 11 Oct 2023, the sequence number is not reset start with "0001". Expected format shall be 10/10 ABC/0001, 10/10 ABC/0002.....etc if there is new date 11 Oct 2023 then shall be 11/11 ABC/0001, 11/11 ABC/0002....etc. Can this be done with formula?
9-Oct-23 09/10 ABC/0001
9-Oct-23 09/10 ABC/0002
9-Oct-23 09/10 ABC/0003
9-Oct-23 09/10 ABC/0004
9-Oct-23 09/10 ABC/0005
10-Oct-23 10/10 ABC/0006
10-Oct-23 10/10 ABC/0007
12-Oct-23 12/10 ABC/0008
13-Oct-23 13/10 ABC/0009
Thank you.
Hi! Based on your second description, it is not about creating a sequence. You need to create code for each new record. You can use the COUNTIF formula for dates to determine the ordinal number of the record within each day. Unfortunately, without seeing your data, it is impossible to offer you a formula.
I need to create a sequence that consists of two columns, the first being Serial No., which contains sequence 1 to 100,000. Columns. 2 is having a code that will have a format based on the serial number value from column 1, i.e., 1 to 1000, code value will be 1, column 1001-2000 column 2 value is 2, which means after each 1000 the code value will change. Can you please help find a simple way to do that?
Hi! You can find the formula for the first column in the article above. For the second column, you can use the rounding to thousands formula:
=ROUNDUP(A1/1000,0)
Or you can use the sequence formula, as has been described many times in the comments to this article:
=CEILING(SEQUENCE(100000,1,1,1)/1000,1)
How wil autogenerate responses for 200 respondents, already have for 55 responses in excel
i have a set of responses from 55 respondents, but i need like 200 ,how will i autogenerate the remaining responses .
Is it possible
300/7/2023
301/7/2023
302/7/2023
303/7/2023
Hi! Create a sequence as described in the article above and add a text string using the & operator.
=SEQUENCE(10,1,300,1)&"/7/2023"
Hello, I have these names in A1:A8 and need to enumerate them on C1:C8, excluding the ones that say treasury. The sequence also depends on the year selected in B1:B8
A B
1 Other property 2023
2 Passenger vehicle 2023
3 Non-passenger vehicle 2023
4 Treasury 2023
5 Other Property 2023
6 Non-passenger vehicle 2024
7 Treasury 2024
8 Non-passenger vehicle 2024
Expected sequence in C
1 01
2 02
3 03
4
5 04
6 01
7
8 02
Hi! The expected sequence you described is not very clear to me. Also note that you will be able to use the year if it is written in a separate column as a number.
Hello, Unfortunately I'm stuck with Excel 2016 and unable to use the SEQUENCE function. Do you have any recommendations for a formula that will create a list of numbers between two values, separated by a certain number? For example, all numbers between 8000 and 10000 seperated by 5. I'll need to do this many times and a formula would be extremely helpful!
Hello! To create a sequence of numbers, you can use the ROW function. The ROW function returns the row number. For example:
=80000+(ROW(A1)-1)*5
Copy this formula down the column.
Hi Alexander,
How to sequence the following please:
K0151/right
K0151/left
K0152/right
K0152/left
K0153/right
K0153/left
I am not good at it. Thank you for your help!
Hi! The formula below will do the trick for you:
="K" & TEXT(CEILING(SEQUENCE(45,1,1,1)/2,1)+150,"0000") & CHOOSE(MOD(SEQUENCE(45,1,1,1),2)+1,"/left","/right")
Hi, How do I sequence with sequence no depends on value on multiple cells/columns, for example:
Col-A Col-B Col-C Col-D (which is the sequence, which value starts can be random value), Thanks a lot!
111 RP C 05
111 RP C 06
AAA DW R 25
111 RP C 07
AAA DW S 55
AAA DW S 56
111 RP C 08
Hi! With Excel formulas, you can only make a sequence of numbers. You have text. Also, there is no pattern in your data that can be written as a formula.
I have a question, please help if you can.
I need a particular pattern in which every number -start from number 1- exist in every successive 5 cells in one column like from A1 to A5 filled with number 1
From A6 to A10 filled with number 2 and so on.
How can I do this?
Thanks in advance
Hi! If I understand your task correctly, try the following formula:
=CEILING(SEQUENCE(50,1,1,1)/5,1)
Hi,
How to convert range (column A1 to B2) into array in single cell for word office 365?
Example:
A1=4
B1=8
Convert array in single cell like this
Result: {4;5;6;7;8}
note:
Previous version word use below formula but not work on office 365.
{ =ROW(INDIRECT(A1&":"&B1)) }
Hi! Brackets are not needed in this formula: =ROW(INDIRECT(A1&":"&B1))
In previous versions of Excel, this formula was written as an array formula. In Excel365 you don't need to do this.
Hello,
I'm after a sequence for creating quote numbers, It would take the Year (2023) Month (04) - then a numerical sequence from 1 down till X.
From the date the quote was provided.
Unless the next month starts, then it would be 2023-05-1, etc
Say Cell Values of 2023-04-1, 2023-04-2, 2023-04-3, 2023-05-1, 2023-05-2, 2023-06-1, 2023-06-2, 2023-06-3, 2023-06-4, 2023-06-5.
3 Quotes in April, 2023
2 Quotes in May, 2023
5 Quotes in June 2023.
Effectively the counter resets at the start of each month.
Thanks
Hi! If there is some logical sequence in your data, describe it. Unfortunately, I do not see it.
Hi Alexander
Logical Sequence - Quote increments by 1 until such time as the month changes, then it resets back to 1.
However, the months do not have a set number of quotes, but will be dictated when a new quote opportunity comes in.
| A | |B|
2023-05 - 1 (May 2023, Quote # 1) Date is 10th May
2023-05 - 2 (May 2023, Quote # 2), Date is 15th May.
2023-06 - 1 (June 2023, Quote #1), Date is 3rd June.
So the number i would like sequenced is made up of 2 components.
Part A is a Function of the date that the quote is created. It uses the Year and Month,
Part B is an incremental step. Starting at 0, it adds an increment of 1 for each new quote created. This continues to increment whilst the Month the quotes are created are the same, when the new month rolls in, the number resets to 0.
So i see the sequence as an increment of 1 integer, then using a while or IF function, Whilst date of creation is X Month add 1 to integer and retain month/year (Part A). If month now is X+1 (May to June), then B=1 and month now set to X+1.
Hi! If I understand your task correctly, the following formula should work for you:
=TEXT(B2,"yyyy-mm")&"-"&(COUNTIF($A$1:A1,"*"&TEXT(B2,"yyyy-mm")&"*")+1)
Use the TEXT function to get the year and month from the date as text. The COUNTIF function will count the number of values with that year and month.
Sir maintaining a large number of business transaction data, we use "purchase" for every purchase transaction, "Sale" for every sale transaction and "Payment" for every payment etc. So i want to prepare a Purchase ledger. for this purpose i want to assign a specific reference number to 1st transaction of Purchase that number should be generated in sequence whenever any other purchase related entry posted.
Example:
5001 Purchase
9001 Sale
5002 purchase
4001 Sale
5003 Purchase
Thanks in advance
Hi! Enter the starting numbers in A1:A2 manually. The formula for A3:
=COUNTIF($B$1:B2,B3) + INDEX($A$1:A2,MATCH(B3,$B$1:B2,0))
After that you can copy this formula down along the column.
I want to create an excel sheet that records dates for maintenance of an equipment but for every time we update the date it should count so we know how many times we have done the maintenance. What can I do? If there are any equations to be written please assist.
I want to make a sequence that looks like this, divided by lines and collums, but it can only have 4 digits, from 1 to 1000
0001 | 0002 | 0003 | 0004 | 0005 | 0006 | 0007 | 0008 | 0009 | 0010 | 0011 | 0012 | 0013 | 0014 | 0015 | 0016 | 0017 | 0018 | 0019 | 0020 | 0021 | 0022 | 0023 | 0024 | 0025 | 0026 | 0027 | 0028 | 0029 | 0030 | 0031 | 0032 | 0033 | 0034 | 0035 | 0036 | 0037 | 0038 | 0039 | 0040 | 0041 | 0042 | 0043 | 0044 | 0045 | 0046 | 0047 | 0048 | 0049 | 0050 | 0051 | 0052 | 0053 | 0054 | 0055 | 0056 | 0057 | 0058 | 0059 | 0060 | 0061 | 0062 | 0063 | 0064 | 0065 | etc
Hi!
If I understand your task correctly, the following formula should work for you:
=TEXTSPLIT(CONCAT(TEXT(SEQUENCE(1,500,1,1),"0000")&" | ")," ")
For more information, please read: Leading zeros in Excel: how to add, remove and hide and TEXTSPLIT function in Excel: split cells / text strings by delimiter.