In Microsoft Excel, the SEQUENCE function is used to generate a sequence of numeric values in different formats. In this article, you’ll learn how you can use this SEQUENCE function efficiently with different criteria and by combining it with other functions in Excel.

The above screenshot is an overview of the article which represents an application of the SEQUENCE function in Excel. You’ll learn more about the dataset as well as the methods to use the SEQUENCE function properly in the following sections of this article.

**Table of Contents**Expand

**Introduction to the SEQUENCE Function**

**Function Objective:**

**The SEQUENCE function is used to create a sequence of numeric values.**

**Syntax:**

**=SEQUENCE(rows, [columns], [start], [step])**

**Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

rows |
Required | The number of rows. |

[columns] |
Optional | The number of columns. |

[start] |
Optional | Start number in the return array. |

[step] |
Optional | The common difference between successive two values in the sequence of numbers. |

**Return Parameter:**

**An array containing a sequence of numbers with the defined specifications.**

**How to Use SEQUENCE Function in Excel: 16 Examples**

**1. Basic Use of SEQUENCE Function with Only One Argument**

The first argument of the **SEQUENCE** function is** ‘rows’** which indicates the number of rows to be shown in the spreadsheet. If you don’t input any other arguments then the function will return the specified number of rows where the first row will contain the number** ‘1’** and later all other sequential numbers will be displayed in the following rows.

So, in the picture below, **Cell B4** contains the formula:

`=SEQUENCE(5)`

**2. SEQUENCE Function with Two Arguments in Excel**

Since the second argument of the function denotes the number of columns, the combination of the first two arguments will result in an array of the specified rows and columns.

In** Cell B4**, the** SEQUENCE** function with rows and columns arguments will be:

`=SEQUENCE(5,3)`

**3. SEQUENCE Function with Three Arguments in Excel**

Now the third argument of the function is **[start]** which denotes the starting value or number to be shown in the first cell of the first row in an array.

So, with the first three arguments, the function will return the array as shown in the following screenshot. And the formula in **Cell B4** is:

`=SEQUENCE(5,3,10)`

Where the starting value is 10 in the array that has been defined in the third argument of the function.

**4. SEQUENCE Function with Four Arguments in Excel**

The fourth argument of the function** [step]** denotes the interval that has to be followed between any two successive values in the array. Assuming that we want to build an arithmetic series of integer numbers starting from 10 where the common difference is 5.

The required formula in **Cell B4** will be:

`=SEQUENCE(5,3,10,5)`

**5. Use of SEQUENCE Function to Generate Dates or Months in Excel**

**i. Creating Sequential Dates with SEQUENCE and TODAY Functions**

The **SEQUENCE** function is very useful for making a number of successive dates starting from a fixed date. By using the **TODAY function** inside as the third argument, we can define the current date which will be the start date. Assuming we are going to create a list of ten successive dates starting from the current date.

The related formula in **Cell B5** should be:

`=SEQUENCE(10,1,TODAY(),1)`

**ii. Creating a List of First Dates for Sequential Months with EDATE and SEQUENCE Functions**

**EDATE function** is used to display the date after or before a specified number of months from a particular date. By combining** EDATE**, **DATE**, and **SEQUENCE** functions together, we can easily create a list of all first dates for several successive months. Let’s say we want to show the first dates of all months in the year 2021.

So, in the output **Cell B4** in the following picture, the required formula will be:

`=EDATE(DATE(2021,1,1),SEQUENCE(12,1,0))`

**iii. Making a List of 12-Month Names with SEQUENCE Function in Excel**

By using the **TEXT function** around the **SEQUENCE **function, we can also prepare a list of successive twelve months in a year.

The required formula in Cell** B5** should be:

`=TEXT(EDATE(DATE(2022,1,1),SEQUENCE(12,1,0)),"mmmm")`

**6. Combination of SEQUENCE and TRANSPOSE Functions in Excel**

By applying the **SEQUENCE **function with all four arguments inside, we can create an array of some sequential numbers and the flow of the numbers will be from left to right like in the picture below.

Let’s assume, we want to display the sequence of these numbers from top to bottom in the array. In this case, we have to use the **TRANSPOSE **function which will convert the rows into columns and the columns into rows.

So, the required formula in the output Cell** B10 **should be:

`=TRANSPOSE(SEQUENCE(5,3,10,5))`

**7. Creating a Sequence of Roman Numbers in Excel**

Creating a list of successive Roman numbers is also possible with the help of **ROMAN **and** SEQUENCE** functions.

The required formula in any cell should be:

`=ROMAN(SEQUENCE(5,3,1,1))`

This will create the fifteen successive Roman numbers starting from** ‘i’ **in the array of five rows and three columns.

**8. Use of SEQUENCE with INDEX Function in Excel**

Now let’s have a look at the dataset below. Each smartphone brand and its model appear twice in the table: one is with the actual price and another is with a discounted price. Let’s assume, we want to show the rows of all brands containing discounted prices only.

In the output** Cell B18**, the related formula will be:

`=INDEX(B6:D15,SEQUENCE(COUNTA(B6:B15)/2,1,2,2),SEQUENCE(1,3))`

After pressing **Enter**, you’ll get the resultant array with all smartphone brands and model names with their discounted prices only.

**🔎**** How Does the Formula Work?**

**➯** **COUNTA **function counts the total number of cells in the range of** B6:B15**. Then the output **(10)** is divided by 2 and the resultant value is inputted as the first argument** (rows)** of the **SEQUENCE** function.

**➯** In the second argument** (row_num)** of the** INDEX** function, the **SEQUENCE **function defines which rows have to be extracted from the table.

**➯** Finally, in the third argument of the **INDEX function**, another SEQUENCE function defines all the columns that have to be considered for extracting data.

**9. Creating a Random Order for SEQUENCE Outputs**

What we have learned so far about the SEQUENCE function is it returns with a specified number of sequential values. But we can also randomize the sequence or the order of the numbers in the resultant array. To do this, we have to use the **SORTBY** function outside the SEQUENCE function and the sorting will be performed based on the **RANDARRAY** function where RANDARRAY function returns random numbers with no particular order or sequence.

In **Cell B4**, the related formula to create a random order for sequential numbers should be:

`=SORTBY(SEQUENCE(10),RANDARRAY(10))`

**10. Creating a Dynamic Calendar with SEQUENCE Function in Excel**

One of the most comprehensive uses of the SEQUENCE functions is preparing a calendar month. Let’s assume, we have a random date value in** Cell C4** and that is **01-08-2021** or **1 August 2021**. By incorporating the **SEQUENCE** and the **WEEKDAY functions** together, we can extract the month from that specified date and thereby show all the calendar days for that particular month.

The required formula to display a calendar month based on a date in **Cell B7** will be:

`=SEQUENCE(6,7,C4-WEEKDAY(C4)+1)`

**🔎**** How Does the Formula Work?**

**➯** In the **SEQUENCE** function, the number of rows has been defined by 6 and the number of columns by 7.

**➯** The start date has been defined by** “C4-WEEKDAY(C4)+1”**. Here the **WEEKDAY** function extracts the serial number of the weekday (By default, **1 **for **Sunday** and thus successively **7** for **Saturday**). The date in **Cell C4** subtracts the number of weekdays and later by adding** ‘1’**, the start date becomes the first date of the prospective month.

**➯** The **SEQUENCE** function then shows the successive dates from left to right in an array of 6 rows and 7 columns. Don’t forget to customize the format of the dates to show only the serial of the days.

**11. Making a Repeated Sequence with the Help of MOD and SEQUENCE Functions**

By using **MOD** and **SEQUENCE** functions together, we can show a particular sequence of numbers or values multiple times in a column, a row or, an array. In the following screenshot, integer values from 1 to 4 have been displayed multiple times in a column.

The required formula in **Cell B4** to create this array is:

`=MOD(SEQUENCE(12)-1,4)+1`

**🔎**** How Does the Formula Work?**

**➯** Since here the integer values from 1 to 4 are to be shown multiple times, the multiple of 4 has to be assigned as the number of rows in the** SEQUENCE** function.

**➯**** “SEQUENCE(12)-1”**, this part of the formula returns the following array:

**{0;1;2;3;4;5;6;7;8;9;10;11}**

**➯**** MOD** function divides each of the integer values in the array with 4 and returns all the remainders in a final array.

**12. Creating Repeated Numbers in a Sequence in Excel**

The combination of **ROUNDUP**, as well as the SEQUENCE function, paves the way to create the repeated numbers in a sequence. In the picture below, integer values from 1 to 5 have been shown twice in succession in **Column B.**

The required formula that has been used to create the return array is:

`=ROUNDUP(SEQUENCE(10, 1, 1/2, 1/2), 0)`

**🔎**** How Does the Formula Work?**

**➯** Here the start point and the step value in the SEQUENCE function have been assigned with **½** in both cases.

**➯** With the mentioned arguments, the SEQUENCE function would return the following array:

**{0.5;1;1.5;2;2.5;3;3.5;4;4.5;5}**

**➯** Finally, the **ROUNDUP** function rounds up all the decimals to the next integer digit.

**13. Generating Empty Values in a Sequence of Numbers**

If you need to leave an empty cell or a space after each value in a sequence of numbers, then you can merge** IF**,** INT**, and** SEQUENCE** functions as well to get the output. In the following picture, the numbers from 1 to 5 have been shown in a sequence with a space after each value in the sequence.

The required formula in **Cell B4** is:

`=IF(INT(SEQUENCE(10, 1, 1, 1/2))=SEQUENCE(10, 1, 1, 1/2), SEQUENCE(10, 1, 1, 1/2), "")`

**🔎**** How Does the Formula Work?**

**➯** **SEQUENCE(10,1,1,½)**, this repeated part of the formula returns the following array:

**{1;1.5;2;2.5;3;3.5;4;4.5;5;5.5}**

**➯**** INT(SEQUENCE(10,1,1,½))** returns another array of:

**{1;1;2;2;3;3;4;4;5;5}**

**➯** With the use of the** IF** function, the formula checks if the values in the second array match with the values in the first one. If the values are matched, the matched rows return with perspective values. Otherwise, the rows remain empty which are considered blank cells in the output column.

**14. Formatting a Sequence of Numbers with Leading Zeros in Excel**

If you know how the** TEXT** function works then formatting a sequence of numbers with leading zeros is too easy. For example, all the numbers in a random sequence will have 4 digits and those of which don’t have 4 digits will contain leading zeros.

The related formula in **Cell B4** will be:

`=TEXT(SEQUENCE(10,1,5,50),"0000")`

**15. Creating a Reverse Order in a List with SEQUENCE Function**

Now let’s assume that we want to reverse the order of the cells in a list. To execute the purpose, we can use the** INDEX** and **ROWS functions** here along with the SEQUENCE function.

In **Column B**, there are some random names and in **Column D**, we’ll display these names in reverse order. So, the required formula in **Cell D5** should be:

`=INDEX(B5:B10, SEQUENCE(ROWS(B5:B10), , ROWS(B5:B10), -1))`

Here, the SEQUENCE function reverses the row numbers of all names and the** INDEX** function later extracts the names in a reverse order based on the second argument **(row_num)** modified by the SEQUENCE function previously.

**16. Preparing a List of Alphabets with SEQUENCE and CHAR Functions**

In the last example, we’ll see how the SEQUENCE function can be used to create a list of alphabets in an array. In the following picture, two separate rows have been used to display the array containing all alphabets.

The required formula in** Cell B4** is:

`=CHAR(SEQUENCE(2,13,65))`

In this formula, the **CHAR function** returns the characters specified by the Unicode. As there are 26 alphabets in English, we have used 13 columns here. We can also define the column number as 2 and the formula will return all the alphabets in 13 rows and 2 columns.

**💡**** Things to Keep in Mind**

🔺 SEQUENCE function returns an array by spilling the values in multiple rows and columns. So, if any of the return values in the array cannot find an empty cell to represent itself then the function will return an** #SPILL** error.

🔺 SEQUENCE function is currently available in Microsoft Office 365 only.

🔺 The default value for all optional arguments of the SEQUENCE function is **1**.

**Download Practice Workbook**

You can download the Excel workbook that we have used to prepare this article.

**Concluding Words**

I hope all of the methods mentioned above to use the** SEQUENCE** function will now provoke you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

**<< Go Back to Excel Functions | Learn Excel**

Didn’t ever notice that using TRANSPOSE on SEQUENCE would be different than reversing row/column values in it ( SEQUENCE(5,3) vs. SEQUENCE(3,5) ). But the reversing goes across columns, then down a row, and repeats while with TRANSPOSE, it goes down a column, over one column, down, and repeats.

I liked especially the generating blanks one. I’m sure there’re lots of ways to do this, but this seems like an easy way to select every n-th item in a column.

The one that gives my heart joy though, since I can use it as a simple, simple, simple way to generate card lists and dice rolls in games, is the random ordering one.

Thanks!

Honorable mention to the {1,2,3,4,1,2,3,4,etc.} one. I know I’ll find a use for that.

Thank You, Roy, for your precious comment!

By the way, the sequence, #9 above, that I will happily use for card lists and dice rolls (and I’m sure other things as well)… of course, due to the RANDARRY function, they are volatile and change upon recalc. Since recalc is hard to avoid when one desires to recalc(!), how does one keep the list as long as it should be kept without somehow converting it to values, then later recovering the formula to use again, all the while not having to do it by hand so as to keep gameplay as gameplay rather than an exercise in mousing about and pasting special and so on? Formulaicly would be nice as one might want to minimize use of VBA, or not use it at all if possible.

Turns out, an old Excel 4 Macro command comes to the rescue.

Excel has recently actually begun truly wiping them out as they gave folks, especially organizations, the ability to exclude them via the Trust Center, but also because, while they would warn you to save as macro spreadsheets (.xlsm, say), you could actually ignore that and save as an .xlsx yet still have everything still there when re-opening the spreadsheet. Not now. If not saved as a macro spreadsheet, they are wiped upon saving.

So, maybe not available for too much longer as these things, and others I may not be aware of, of course, are direct attacks on people using them with the obvious intent of, one day soon (sooner, not later), eliminating their existence.

It’s been 25-30 years without much more than neglect, but I wager that within two new versions, they will be utterly gone.

In any case, they exist now. And if one sets the formula (#9’s in this case) in a Named Range, then uses a second Named Range to EVALUATE it, then the result of that second Named Range is NOT ONLY non-volatile, but it works with SPILL array functionality so all the elements of the array are present for other formulas to use or to display in a list as you’d expect, if you wish to do that.

So using that trick, which I wager would be good for other volatile functions since SEQUENCE is a modern, cell-side function itself, one gets a stable array even though one of the elements of the array’s formula, RANDARRAY, is itself volatile.

Not related, but something else representing a still useful difference in functionality, is that some Excel 4 Macro commands have modern, cell-side functions that replace them. However, the new functions often have limitations in respect to the earlier functions and those differences can make a… difference.

Using CELL(“width”) gets you an integer value for the cell width whatever its true width is. GET.CELL(16,A1) shows the measurement you’d see examining it’s properties, so if 8.63, then 8.63, not 8. Minor, but normally what you actually want.

Modern, cell-side FORMULATEXT returns the formula in a cell, including “=” sign, IF ANY FORMULA IS PRESENT, as text for use. If any formula is present, and errors otherwise. But GET.CELL(6,A1) or GET.CELL(41,A1) return whatever is in the cell, so if the constant value 8 is there, “8” is returned, not an error. A nice way to avoid error handling, if nothing else.

Not to mention that since I now have to save as a macro file anyway, there’s no inhibition to using the old macro sheets (Ctrl-F11 to insert one). VBA is much mor ecapable, no question, but harder to use. Macro sheets from “back in the day” are pretty simple to write with new functions easy to find and understand while VBA references… not so much, really. And seconday considerations are often needed with VBA as well. Specify a range? Oh, just type A1:A4. Oh. No? Not even close? Range(A1:A4)? Good… Oh… no? Range(“A1:A4”)? Good… Oh… no? Ah, .Range(“A1:A4”). Yay! I think… yes, seems that works… secondary knowledge is needed for almost every function or command. Not so with Excel 4 Macros.

But… oh no… they are really going away… THAT’s an inhibition. (Sigh…)

Still, while they still work… For the differences!

Especially until they give us a cell-side EVALUATE, and the glorified LET they call LAMBDA does NOT look like that will do, I hope they keep the Excel 4 Macro commands working. EVALUATE is soooo much more than INDIRECT. There’s really little comparison.

Lordy, LAMBDA. Been making Named Range functions, as in they can take parameters, for 25 years now. LAMBDA will make that easier (literally a million times easier, very little exaggeration here, and why I have not made thousands in that time, rather a couple dozen only) and better, sure, NO question at all, but it isn’t new capability! And not much differnt than LET already is. Marketing… (shudder).

Hello Roy! Thank you for your comment. I must say your knowledge of Microsoft Excel is really appreciable. Keep commenting on posts of Exceldemy and help others to learn more.

Is it possible to make in sequence formula in excel 100A, 101B, 102C, 103D, 104A, 105B, 106C, 107D so on and on. Thanks

Hello Johan, you can use the following procedure to serve your purpose-

– Make a column with 101, 102, 103, and so on.

– Make a column with A, B, C, D, and repeat.

Follow this article to repeat numbers or characters serially.

How to Add Numbers 1 2 3 in Excel

– Then use CONCATENATE function to combine them.

Let us know the outcome in the reply. Thank you!

Trying to create a sequence formula to insert a date(s) to begin at 1st day of month corresponding to day of week in a column. I have a worksheet with (12) different sequence formulas, one for each month, based on a given “input” year. Would like to only populate actual dates in each month. Right now am populating all (37) possible day options in columns.

I can send you a screenshot or copy of present file. Thanks for any suggestions.

2-19-2023

Good day, Don Rogers.

Thank you for your feedback. Could you please share your excel file with me so that I can better understand your problem and provide you with a solution? You can shareyour file to the email address provided below.

[email protected]

Thank you!

Here is another example that will fill a list with the same value is growing dynamically with the list size.

=SWITCH(SEQUENCE(COUNTA(a_list)-1),1,”Word_to_repeat”,”Word_to_repeat”)

Hey Yann, we have tried your formula. But unfortunately, this formula is not working as you said it would. It gives a Value error instead.

Hi, how can I extend the INDEX & SEQUENCE function with a COUNTIF that there then (0/1/2/etc.):Text-of-LIST!B2:B30

[1:volcano]

[1:village]

[1:city]

[2:village]

comes out

=INDEX(List!$B$2:$B$30;SEQUENCE(ANZAHL2(List!$B$2:$B$30)/1;1;1;1);SEQUENCE(1;1))

=COUNTIF(List!$B$2:B2;List!B2)&”:”&List!B2

Hello

DOMJI,I cannot understand why are you trying to combine these two formulas. You can get your desired output just by using the formula with the

COUNTIF function.`=COUNTIF($B$2:B2,B2)&":"&B2`

Using this formula, you can get the

[Occurrence: Item]format in the output. You cannot use theCOUNTIF functionwithINDEXandSEQUENCEfunctions as they return array output.Hope this helps. Let us know if you have any other things to know.

Regards,

SHAHRIAR ABRAR RAFIDTeam

ExcelDemy