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)
431 comments
Hi, I'm looking for help in creating an excel pay scale across 100 slots.
Ideally It would start with an entry level annual pay (ex: $27,142) and a senior level pay (ex $151,894)
Id like A-1 to be 27142 and A-100 ends with 151894 with the numbers as evenly spread as possible across the remining 98 slots. Is this possible?
thank you!
Hello Timothy!
Create a sequence of numbers as described in the article above. Round the results using ROUND function.
Based on the information given, the formula could be as follows:
=ROUND(SEQUENCE(100,1,27142,(151894-27142)/99),0)
Hi sir,
I'm trying to sequence is like (1-7)in a row, and continue with a jump of 14 digits which is (29-35), so it will look like:
1
2
3
4
5
6
7
29
30
31
32
33
34
35
57
58
59
60
61
62
63
...etc.
Do we have any way by using sequence to do it?
Hello Adam!
Create two numeric sequences and summarize them. In the second sequence, use CEILING function to change the date after 7 days immediately to 14 days.
=SEQUENCE(30,1,1,1) + (CEILING(SEQUENCE(30,1,1,1)/7,1)-1)*21
A001-01
A001-02
---
A001-50
A002-01
A002-02
---
A002-50
Hi! Create two sequences of numbers using SEQUENCE function. To create a sequence of repeating numbers use the CEILING function.
TRUNC and MOD functions are used to create a cyclic sequence of numbers.
To show leading zeros in the numbers, use these instructions: How to add leading zeros in Excel with the TEXT function.
="A" & TEXT(CEILING(SEQUENCE(100,1,1,1)/50,1),"000") & "-" & TEXT(TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,50)+1),"00")
Combine these values and add the desired characters using this article: CONCATENATE in Excel: combine text strings, cells and columns.
Thank You Sir!
It was very helpful
Sir!
Is there a way to generate this pattern in ms access using VBA or Marcos
Hi! We do not do VBA code creation or customization on request.
I would like to use sequence to create a dynamic array that propagates an array formula across a defined number of columns and rows. For example I have list of values (horizontally) starting in B1 and a list of values (vertically) starting in A2 and have the formula =B$1*$A2 in cell B2. How do I use sequence to make and array of the formula in B2.
Thanks.
Hello Stephen!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:
=TOCOL(B1:D1*A2:A5,,TRUE)
To understand this formula, I recommend this tutorial: Excel TOCOL function to convert range to single column.
Thanks Alexander,
Sorry, I am trying to simplify the overall problem to by simplifying some of the variables but keep the same basic logic. My spreadsheet currently creates a list of header in the top row, (based on a start and end date) and generate a list of names in the first column, based on the same dates. There is then an array formula that covers B2:S50 that does an index/match lookup to draw a value from a database sheet.
I don't want to go into the specifics of the formula that propagates across the array suffice as to say that, ultimately I would like to, for reporting purposes make the range of the array equal to that for the filter header and side row. I can achieve this fairly easily by grabbing the array off an intermediary worksheet, but I would prefer to bake the logic into a single worksheet if I can.
I am not sure if that helps explain what I am trying to do.
Thanks again.
Hi! Unfortunately, I'm still not very clear about what you're trying to achieve. My guess is that you can use the FILTER function to search only part of the array. Look for the example formulas here: Excel FILTER function - dynamic filtering with formulas.
If this is not what you wanted, please describe the problem in more detail.
Thank Alexander,
I'll try give more detail. A filter array may be the correct approach, but it's a relatively complex formula already. As described previously the worksheet (which is an attendance register of sorts) shows a collection of information that it is pulling from a separate sheet in the same excel file, let call the source information "member database". The member database is basically a historical list of all the members of the organisation dating back to 2015, with information columns currently spanning column B - RC. The columns are organised in sections but principally the columns contain dates that relate to when a member would have completed whatever that column represents.
These columns are arranged in sections, as mentioned, where the earlier columns relate to when a member joining or leaving the organisation (among other things) and this information is used by the register determine the specific list of members to include when reporting on a specific date range.
The next couple of sections relate to the dates at which individual members are award specific awards or advancements. And the final columns are all the dates of each of the meetings held by the program with a true/false/nothing recorded in the columns on a per member basis.
In terms of reporting, a report (register) using a data validation list, a user can select the year and term for the report. By selecting the year and term the report can determine a start and end date for reporting, which then generates a list of all the members who were active during that period which it displays in the first column of the register. It also generates a list meeting dates that it displays along the top row of the register, this results in the number of items in each list being variable.
The last component of the register is an array of cells that collect a variety of infomation from the database and display it in the cells in a comma delimited strings. Specifically it gets 3 sets of information, the first being a true/false/none value (was the member present at the meeting), second it looks at the dates in the section that relate to any awards that were earned between the previous meeting and this one, lists the names of those awards that relate to those dates. and then does the same thing for advancements.
At present my formula to create this list does work. and it is properly set up so that if I drag the formula across the array it correctly populates the entire array.
The issue is that this dragged array needs to be setup to accommodate the largest array that could potentially be selected, so I would like to be able to make this array of formulas into a single spilled array that dynamically resizes itself so that I can incorporate it more seamlessly into a bigger report that uses H and VSTACK.
While it may be possible to convert my current formula into a filter formula, given the already complex nature of it, I was hoping I could effectively wrap it in a sequence that generates the formula across much like a =SEQUENCE(counta('memberlist),counta('meetinglist),1,1) would generate an array that is 'memberlist rows and 'meetinglist wide. but instead of filling it with 1-100 it fills it with my existing formula.
My apologies for the long explanation, but I hope this helps make the bigger picture of what I am trying to achieve understandable.
Thanks again.
Hi! Unfortunately, without seeing your data, it is difficult to give you advice. You may need to use a dynamic data range that changes automatically depending on the data you are using.
Here are instructions to help with that: Excel OFFSET formula to create a dynamic range and INDEX formula to create a dynamic range and Structured reference in Excel tables.
I want to create a Spreadsheet that will generate a 6 digit P.O. in the first column only after there is text in the second column describing the work. I want to begin with example: 025100.
Hello Dean!
The answer to your question is found many times in the comments below this article. Create a sequence of numbers and add leading zeros as described in this tutorial: Leading zeros in Excel: how to add, remove and hide. For example:
=TEXT(SEQUENCE(10,1,25100,1),"000000")
Hello, I would like some help to make the following sequence on excel could you help please.
We have aisles in our warehouse one side of the aisle is odd numbers and the other side is even numbers,
The first 01 is the aisle number then the following numbers 01,03,05 is the location number which goes up sequential, the letter is then the location height (A on the floor, B then following then C AND D.)
01-01-A
01-03-A
01-05-A
what would the formula be for this on excel, Thanks!
Hello Bradley!
For the data you have provided, you can use methods that are described many times in the comments below. Create a sequence of numbers and combine with text strings.
="01-"&TEXT(SEQUENCE(3,1,1,2),"00")&"-A"
Also use this guide: Add leading zeros to numbers (TEXT function).
I am trying to create an incremental sequence of labels for an electrical excel sheet. I have rows numbered from 1 to 156 the need to read as 1+-1-, 2+/2-, 3+/3-...and so on and I have multiple files I need to do this for. How do I get excel to count up numbers but leave the characters the same?
Hello Robbie!
Create two sequences of numbers using the SEQUENCE function and combine them with text using concatenation operator (&).
=SEQUENCE(156,1,1,1)&"+/"&SEQUENCE(156,1,1,1)&"-"
You can also use ROW function to create a sequence of numbers. For example:
=ROW(A1:A156)&"+/"&ROW(A1:A156)&"-"
I am trying to stack two sequences. They are both single column instances, but I want them stacked vertically, with independent input, so I can use the output in a mail merge.
Like this:
Seq 1
Seq 1
Seq 1
Seq 1
Seq 2
Seq 2
Seq 2
If I concat them, I get this instead:
Seq 1Seq 2
Seq 1Seq 2
Seq 1Seq 2
Seq 1Seq 2
This is my formula right now: =SEQUENCE(Z2,1,AA2,0)&(SEQUENCE(Z1,1,AA1,0)) that is producing them cojoined... how do I make them stack vertically instead?
Hi! If I understand your task correctly, you can combine two sequences using the VSTACK function. For example:
=VSTACK(SEQUENCE(5,1,1,1),SEQUENCE(5,1,10,1))
The following tutorial should help: How to combine ranges / arrays in Excel with VSTACK & HSTACK functions.
How do I create a sequence that takes the first item in a series and adds it to each item in another series, then takes the second item in the first series and adds it to each item in the other series, until all items in the first series have been done.
E.g.
Series 1
P001
P002
[...]
P040
Series 2
H001
H002
[...]
H040
New series
P001H001
P001H002
P001H003
[...]
Poo1H040
P002H001
P002H002
P002H003
[...]
P040H039
P040H040
Hello Alycia!
Use TRANSPOSE function to arrange the second array on a line. Use concatenation operator (&) to combine the values of the two arrays. Use TOCOL function to convert a range of values to a single column.
The formula might look like this:
=TOCOL(A1:A3&TRANSPOSE(B1:B3))
Trying to create an auto populated PO# system in Excel.
Sheet 1 - Column A is where the PO # needs to populate. Column B is the Customer Name (matches Sheet 2, column A)
Sheet 2 - Column A is the Customer Name, Column B is the letter sequence that customer's PO# should start with.
What formula do I put in Sheet 1, Column A for this to happen?
Example: someone will open spreadsheet and go to sheet 1. In column B they will type in customer name. Once they hit enter or tab to the next column, I want the PO# to automatically generate into column A.
Sheet 2, Column A: Customer Name: Kroger Sheet 2, Column B - PO Starting # - KRO
Result should be:
I enter the Customer Name in Column B on sheet 1 as Kroger and the PO# in Column A on sheet 1 would populate with KRO followed by a number
If I understand the question correctly, you want to use the first 3 letters of the Customer Name. To these letters you can add, for example, the number of the row.
Hello Marie!
Use IF function to check that cell B1 is not an empty cell. Extract the first 3 characters using LEFT function and change case using UPPER function.
Get the row number using the ROW function and convert it to a text string in the format you want using the TEXT function.
For example:
=IF(B1<>"",UPPER(LEFT(B1,3))&TEXT(ROW(),"0000"),"")
Hello I am creating a editable rolling calendar , here is what i have :
31-Dec,1-Jan,2-Jan,3-Jan,4-Jan,5-Jan,6-Jan
7-Jan,8-Jan,9-Jan,10-Jan,11-Jan,12-Jan,13-Jan
But i wish it to have an empty row below each row with content, like this:
31-Dec,1-Jan,2-Jan,3-Jan,4-Jan,5-Jan,6-Jan
(empty row)
7-Jan,8-Jan,9-Jan,10-Jan,11-Jan,12-Jan,13-Jan
(empty row)
With the sequence formula, i cnt insert and delete rows, but i will need the sequence formula remain to make it dynamic
any suggest do you have to solve this?
hi
bill number auto
225/24-25
226/24-25
227/24-25
Hi! Create a sequence of numbers and combine them with a text string. See the following comment below as an example.
Hi, i have created a quote form and want to create a sequential system where each time the form is used its the next number, is this possible
Example
Q101
Next time Q102 etc
Hi David!
Pay attention to the comments below. There are many of the same questions with answers. Create a sequence and concatenate numbers into a text string. Read more: CONCATENATE in Excel: combine text strings, cells and columns.
="Q"&SEQUENCE(10,1,101,1)
I want to put in column value 1,2,3,4 then down row i want 5,6,7,8
For exp.-
1,2,3,4,
5,6,7,8
9,10,11,12
Hi! Create four number sequences and concatenate the numbers into a text string. Read more: CONCATENATE in Excel: combine text strings, cells and columns.
=SEQUENCE(10,1,1,4)&","&SEQUENCE(10,1,2,4)&","&SEQUENCE(10,1,3,4)&","&SEQUENCE(10,1,4,4)
NC000001
NC000002
NC000003
NC000004
NC000005
NC000006
Hello sir, how do I make this in sequence in 500 times
Hi! If you read the comments below, you will find several answers to your question. Create a sequence of numbers, add leading zeros using the TEXT function and combine with a text string.
For the detailed instructions, please see: Add leading zeros to numbers.
Based on this information, the formula could be as follows:
="NC"&TEXT(SEQUENCE(500,1,1,1),"000000")
I have a lot of difficulty doing something. I would like to calculate the cusum of a variable bunch of row automatically. To do so, I tried to create a sequence based on every previous value of the same sequence and another value on tha actual row (because the cusum is the sum of the gap with average). Is there a way to put something for the "step" argument of the sequence function so it takes relative reference from every item in itself ? Otherwise, How could I calculate the Cusum of an array into another array dynamically ? Thanks a lot !
Sorry, I do not fully understand the task. To understand what you want to do, give an example of the source data and the expected result.
Hi,
could You help me sequence every text/number in a row for 31 times each of them in single colomn
ex.
230502234
221101338
221101344
230502203
230702549
thanks in advance
Hi! If there is a pattern in your numbers, describe it. I don't see any such pattern, and I don't understand what it is you are trying to do.
every data (text/number) in a row Column A should be generate 31 times to a single column every text/number to column B
Column A Column B
230502234 ----> 31 times
221101338 ----> 31 times
221101344 ----> 31 times
230502203 ----> 31 times
230702549 ----> 31 times
230502234
230502234
230502234
230502234
221101338
221101338
221101338
221101338
221101344
221101344
221101344
221101344
and so on
Hi! To repeat a number many times, use a step of zero. Since your numbers have no pattern, you need to create a separate SEQUENCE formula for each number. For example:
=SEQUENCE(31,1,230502234,0)
Hello sir plz solve this problem in excel formula
1
1
1
1
2
2
2
2
3
3
3
3
4
4
4
5
5
5
5
is there a way to do this in excel? thanks
Hi! For an answer to the exact same question, see the two comments below.
Hi i am having trouble trying to perform the task i want.
i have the sequence formula here "=SEQUENCE(1,B10, C6, 1)" where B10 is the total number of days and C6 is the start date 29/05/2022.
my sequence currently is 29/05/2022, 30/05/2022, 31/05/2022 etc
but i want to make my sequence like this 29/05/2022, 29/05/2022, 29/05/2022, 29/05/2022, 29/05/2022, 29/05/2022 , 30/05/2022, 30/05/2022, 30/05/2022, 30/05/2022, 30/05/2022, 30/05/2022 etc.
So the dates repeat 6 times before moving forward. Is it possible?
Hi! The answer to that question is right below your comment. I recommend reading it. Use a date instead of a number. You can set the start date using the DATE function: DATE(2022,5,29).