How to Use SEQUENCE Function in Excel (16 Examples)

Here’s a brief overview of SEQUENCE(5,3,10,5). To learn exactly how we got there, follow the instructions below.

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:

• Return Parameter:

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

How to Use SEQUENCE Function in Excel: 16 Examples

Part 1 – Basic 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, the function will fill in cells in the specified number of rows where the first cell 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:

Part 2 – SEQUENCE Function with Two Arguments in Excel

Since the second argument of the function denotes the number of columns, using two arguments will result in an array of the specified rows and columns, sequenced from left to right then top to bottom.

Consider this example where the Cell B4 contains the SEQUENCE function with rows and columns:

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

Consider the following formula in Cell B4. With the first three arguments, the function will return the array as shown in the following screenshot.

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

Part 4 – SEQUENCE Function with Four Arguments in Excel

The fourth argument of the function, [step], denotes the interval between any two successive values in the array.

Suppose we want to build an arithmetic series of integer numbers starting from 10 where the step is 5.

The required formula in Cell B4 will be:

Part 5 – Use of SEQUENCE Function to Generate Dates or Months in Excel

Method 1 – Creating Sequential Dates with SEQUENCE and TODAY Functions

Suppose we want to create a list of ten successive dates starting from the current date. The related formula in Cell B5 should be:

Method 2 – Creating a List of First Dates for Sequential Months with EDATE and SEQUENCE Functions

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:

Method 3 – Making a List of 12-Month Names with SEQUENCE Function in Excel

The required formula in Cell B5 should be:

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

Suppose we want to display the sequence of these numbers from top to bottom in the array. The required formula in the output Cell B10 should be:

Part 7 – Creating a Sequence of Roman Numbers in Excel

The required formula in any cell should be:

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

Part 8 – Use of SEQUENCE with INDEX Function in Excel

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. Here’s how you can show the rows of all brands containing discounted prices only.

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

• Press Enter, and you’ll get the resulting 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.

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

Part 9 – Creating a Random Order for SEQUENCE Outputs

For 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:

Part 10 – Creating a Dynamic Calendar with SEQUENCE Function in Excel

Assume we have a random date value in Cell C4 and that is 01-08-2021 or 1 August 2021. By incorporating the WEEKDAY function, 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:

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.

Part 11 – Making a Repeated Sequence with the Help of MOD and SEQUENCE Functions

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:

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.

Part 12 – Creating Repeated Numbers in a Sequence in Excel

In the picture below, integer values from 1 to 5 have been repeated twice in Column B.

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

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.

Part 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:

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

Part 14 – Formatting a Sequence of Numbers with Leading Zeros in Excel

Let’s format the output to contain leading zeroes for four digits. The required formula in Cell B4 will be:

Part 15 – Creating a Reverse Order in a List with 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:

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.

Part 16 – Preparing an Alphabet List with SEQUENCE and CHAR Functions

In the following picture, two separate rows have been used to display the array containing all letters.

The required formula in Cell B4 is:

In this formula, the CHAR function returns the characters specified by the Unicode. As there are 26 letters in English, we have used 13 columns here. We can also define the column number as 2 and the formula will return all the letters 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.

<< Go Back to Excel Functions | Learn Excel