SEQUENCE Function – Breakdown

SEQUENCE function is one of the most advanced and modern functions of Excel. It was introduced as a part of the dynamic array tools and is only accessible to Microsoft 365 users.

To learn more about this intelligent array tool, continue reading.

Keep yourself updated on all the latest functions of Excel by accessing our top-class Excel courses here.

 

Excel SEQUENCE Function

SEQUENCE function in Excel is one of the dynamic array functions introduced through Microsoft 365. It is only available in Excel 2021 and later versions.

The Excel SEQUENCE function generates a list of sequential numbers organized in the shape of an array. This array can be single or multi-dimensional depending upon the number of rows and columns arguments.

Syntax      

Syntax of the SEQUENCE function reads as follows.

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

Arguments

Let’s break down the above arguments to see how each of these works.

• • Rows – This specifies the number of rows to be returned.
  • Columns – This specifies the number of columns to be returned.
  • Start – Optional argument. It enables users to specify the starting point of the range.
  • Step – Optional argument. It enables users to specify the interval between the values in the range.

Return Value

The SEQUENCE function returns a series of sequential values based on the arguments input into the formula. These numbers can be in the form of an array or a range.

Functions Library

You may access the SEQUENCE function from the Functions Library as follows.

Formulas > Functions Library > Math & Trigonometry Functions > SEQUENCE

 

Understand further details of formulas and functions in Excel here.

 

Why would anyone want to use it?

You may want to use the SEQUENCE function for a wide variety of causes. For instance, if you want to set up a duty chart for your co-workers. Let’s say, each co-worker gets to work for an extra two hours once every week

By specifying the rows as 4, columns as 7, a start and step value, you can set up a work calendar to determine the day each co-worker gets to work extra.

 

SEQUENCE Use Cases

The SEQUENCE function turns out to be the product of a very intelligent mind. Using this function, you can quickly set a single or multi-dimensional array. A few practical uses of the SEQUENCE function are listed below.

• A major use of the SEQUENCE function is the setting up of a calendar. Using the SEQUENCE function, you can set up a monthly or yearly calendar in minutes.
• When combined with other functions such as SUMIF and COUNTIF, the SEQUENCE function can be used to form reports. For instance, if you have the sales records for a year. Sales made during N number of days can be computed using the SEQUENCE function.

N can be any number of days, be it 7, 15, or 30 to be specified as the step value. You can then also create flowcharts in Excel using sequential number series.

• The SEQUENCE function is largely used for the real-life grouping of items. For example, to draw a sample out of a population, you can put together the SEQUENCE function. It helps you draw every Nth item from a population.

Where N is the interval to be specified.

That broad list of uses of the SEQUENCE function doesn’t end here. It can be used for a variety of tasks, either solely or in combination with other functions. Common functions paired with the SEQUENCE function include TEXT, DATE, TODAY, YEAR, etc.

Get to know more about logical functions in Excel by clicking here.

 

Using SEQUENCE to generate an array of sequential numbers – Examples

To understand this function better, let’s throw together a basic example of how you may use the SEQUENCE Function in Excel.

 

Basic Example

Let’s begin with a basic two-dimensional array that is 4 rows tall and 7 columns wide. How to do that? To do so, compose the SEQUENCE formula as follows.

= SEQUENCE (4, 7, 1, 1)

• • The first argument i.e. rows are set as 4. This is because we want the resultant range to be 4 rows tall.
  • The second argument i.e. columns are set as 7. This is because we want the resultant range to stretch across 7 columns.
  • The third argument i.e. start is set to 1 as we want the range to begin from 1. This argument is an optional one that can also be omitted. Excel would still assume it to be 1 if left vacant.
  • The third argument i.e. step is set to 1 as we want the interval between the values to be 1. This argument is an optional one that can also be omitted. Excel would still assume it to be 1 if left vacant.

The cell where you enter the formula will act as the first cell of the row and column where the range or array is to be populated.

Activate a cell, type in the formula above, and press ‘enter’ to yield results as follows.

 

Using the SEQUENCE function in Excel is only that easy.

 

More Examples

After we’ve come across a basic example to create a range or array in Excel using the SEQUENCE function, it’s time we move forward. Let us build some more examples to see into the technicalities that come along with the SEQUENCE function.

 

Positive and Negative Values

Is the SEQUENCE function adaptable to both positive and negative values? Yes, it is. All you need to do is work out the syntax smartly.

For example, let’s build an array that extends from -5 to +5. To do so, here’s how the SEQUENCE function is to be composed.

= SEQUENCE (10, 1, -5, 1)

• • As we want the array to extend from -5 to +5 (that makes a total of 10 numbers), we’ve set the number of rows to 10.
  • The column’s argument is set to 1.
  • The start argument is set to -5.
  • The step argument is set to +1.

Hit ‘Enter’ to see Excel generate a sequential series of numbers ranging from -5 to +5.

 

Value countdown

We have seen how the SEQUENCE function works with generating sequential numbers in ascending order. But what if we need a sequential series of numbers that goes the other way around?

To generate sequential numbers in descending order, the SEQUENCE function needs to be slightly amended as follows.

For instance, to generate an array of descending numbers ranging from 50 to 40, you may set up the SEQUENCE formula as follows.

= SEQUENCE (1, 10, 50, -1)

• • The number of rows is set to 1.
  • As we want the array to extend from 50 to 40 (that makes a total of 10 numbers), we’ve set the number of columns to 10.
  • The starting point is set to 50.
  • Each step is set to -1. Every subsequent value will be one less than the previous value.

Hit ‘Enter’ to see Excel generate a series of numbers in descending order.

 

Date Generation

Dates in Excel are only serial numbers. Surprisingly, the beginning point of this series is 01 January 1900. Excel takes the said date equivalent to 1, and every successive day keeps adding to it.

Using the SEQUENCE function, you can create a sequential series of dates and add any number to it. Take a look below.

The image above represents the date when Summer Camps in XYZ School are to begin. They are going to last for good 20 days. To build a series of dates till they continue, write up the SEQUENCE function as follows.

=SEQUENCE (5, 4, D1, 1)

• • The number of rows is set to 5 through the first required argument.
  • The number of columns is set to 4 through the second required argument.
  • The starting point is set equivalent to cell D1 where the starting date is populated.
  • The step is set to 1. This being an optional argument may be left vacant.

Hit ‘Enter’ to see the results as follows

 

Excel generates a series of dates beginning from 01 June 2020 up to 20 June 2020.

Pro Tip: If your results look like a list of serial numbers and not dates, that’s because the cells are not rightly formatted. Select them all and change their format to ‘Date’ from the tab Home > Number.

When not formatted as dates, Excel takes the dates as serial numbers instead.

 

Workdays Generation

Let’s go a step ahead with the same example stipulated above. What if you want to determine only 20 workdays starting from 01 June 2020?

To do so, we need to nest the SEQUENCE into WORKDAY.INTL function, as follows.

= WORKDAY.INTL (D1-1, SEQUENCE (5,4))

• • D1 represents the starting date. The WORKDAY.INTL formula starts counting the working days from the date following the starting date. We have deducted one day from the starting date to combat the same.
  • The SEQUENCE function is set up with 5 rows and 4 columns. The last two optional arguments are omitted and would be set to 1 by Excel by default.

 

This generates twenty workdays starting from 01 June 2022. The WORKDAY.INTL automatically excludes Saturdays and Sundays being weekends.

Once created, you may choose to name a range of cells for ease of reference. To practice naming a range in Excel, click here.

 

Time Series

The SEQUENCE function can be nested into the Time, Hour, Minute, or Second function.

For example, let’s say you want to generate a time series of 5 times, each 3 hours apart, starting from 3 pm.

This can be achieved through a combination of three functions i.e. the HOUR, SEQUENCE, and TIME function. Follow the steps below to see how the same can be done.

 

Step 1:

The very first formula that needs to be put together is the HOUR formula.

= Hour (“3:00PM”)

The Hour formula converts the time into serial No. 3.

Step 2:

Next, we need to formulate the SEQUENCE formula for a time series of 5 times.

= SEQUENCE (1, 5, Hour (“3:00PM”), 3)

• • • We need the time series allotted horizontally, so the first argument i.e. rows is kept as 1.
    • The first argument i.e. columns are set to 5 as we need a series of 5 times.
    • In place of the third argument, the hour formula is nested as the SEQUENCE formula requires it to be in the form of a serial number.
    • As we need the time series to be plotted at the gap of 3 hours, the fourth argument i.e. step is set to 3.

Step 3:

Until now, if you press enter, Excel would return a series of numbers starting from 3 up to 5 sequential numbers with an interval of 3.

However, as we want a time series, these serial numbers must be presented in the time format i.e. 00:00 PM.

The syntax of the time function is as below.

= TIME (Hours, minutes, seconds)

To change the return value of the SEQUENCE function from serial numbers to time format, we need to nest in the formula above in the Time function as shown below.

= Time (SEQUENCE (1, 5, Hour (“3:00PM”), 3), 00, 00)

• • • The time function changes the return value of the SEQUENCE function into 00:00 PM format.
    • The further zeros at the end of the formula represent the last two arguments from the time function i.e. minutes and seconds. For simplicity, both are kept equivalent to 00.

Step 4:

Once the formula is all set, hit ‘Enter’ to yield the desired time series, as follows.

 

To ensure precise operations, try auditing formulas in Excel for accuracy.

 

More about the SEQUENCE function:

This is not it. You can nest the SEQUENCE function into many other Excel functions to yield a variety of useful results. You may use it together with TEXT, EDATE, INDEX, SUMPRODUCT, DATE function, and so many more functions.

Ever had Excel crash after a hard days work on your sheet? Our guide to Recovering Unsaved Files here will stop this heart breaking moment!

 

SEQUENCE Troubleshooting

The SEQUENCE function itself is a relatively straightforward function where you are likely not to confront problems.

However, a few common problems faced by users are enumerated below.

 

1.   The #SPILL! Error

If you are new to the dynamic array functions, the #SPILL! error may sound alien to you. Don’t fret if Excel returns you the same when operating the SEQUENCE function.

The SEQUENCE function returns an array or range of numbers split into multiple columns and rows (as defined through the arguments).

Let’s say you formulate the SEQUENCE function in Cell B2 that is 4 rows tall and 5 columns wide. This would stretch the resultant array from cell B2 to cell F5.

If any cell in between this range (B2 to F5) is not vacant to populate the resulting range, Excel would pose the #SPILL! error.

For instance, in the image below, Excel gives back #SPILL! error as Cell C3 already consists of content.

 

To deal with the said error, remove the data in cell C3 and try again as follows.

 

 

2.   The #VALUE! Error

All the arguments within the SEQUENCE function tend to be numeric values. If any of these arguments is configured as a text value, Excel would return the #VALUE! Error.

To resolve the same, recheck the SEQUENCE formula to look out for any text arguments.

 

Conclusion:

The examples above emphasize how the SEQUENCE function works. Also, once you are good at nesting the SEQUENCE function into other functions, you can work out this array tool endlessly.

Practice the above examples to master the array tools of Excel.