How to Use SEQUENCE Function in Excel (16 Examples)

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.

sequence function overview in excel

The above screenshot is an overview of the article which represents an application of the SEQUENCEfunction 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.


Download Practice Workbook

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


Introduction to the SEQUENCE Function

sequence function syntax

  • 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.


16 Examples of Using SEQUENCE Function in Excel

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)

sequence function with one or single argument in excel


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)

sequence function with two arguments in excel


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.

sequence function with two arguments in excel


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)

sequence function with four or all arguments in excel


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)

creating next ten sequential dates with today functions in excel


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))

creating first dates of 12 sequential months in excel with sequence edate date functions


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")

creating sequential 12 months with sequence text edate date functions in excel


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.

use of sequence and transpose in excel

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))

use of sequence and transpose in excel


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.

creating sequence or series of roman numbers in excel


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 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.

use of sequence and index functions in excel

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.

use of sequence and index functions in excel

🔎 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 the 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))

creating numbers in random with sequence sortby functions in excel


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)

creating dynamic calendar with sequence function in excel

🔎 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

creating repeated sequence with mod and sequence functions in excel

🔎 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 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)

creating repeated numbers in a sequence with roundup function in excel

🔎 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), "")

generating empty values in a sequence of numbers in excel

🔎 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")

adding leading zeros in a sequence of numbers in excel with text function


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))

creating list in reverse order with index sequence functions in excel

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))

creating list of alphabets with char and sequence functions in excel

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.


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.


Further Readings

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

2 Comments
  1. 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.

  2. 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).

Leave a reply

ExcelDemy
Logo