# SEQUENCE function in Excel The SEQUENCE function is one of the new functions which Microsoft released as part of introducing dynamic arrays. This function makes use of changes to Excel’s calculation engine, which enables a single formula to display (or “spill” if using the new terminology) results in multiple cells.  In the case of SEQUENCE, it will generate a list of sequential numbers in an array.

At the time of writing, the SEQUENCE function is only available to those on a Microsoft 365 subscription.  It will not be available in Excel 2019 or earlier versions.

I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

• My favorite tips and tricks direct to your inbox
• Exclusive content (which is only available to subscribers) The filename for this post is 0037 SEQUENCE function in Excel.xlsx

Contents

## The arguments of the SEQUENCE function

The SEQUENCE function has four arguments:

`=SEQUENCE(Rows, [Columns], [Start], [Step])`
• Rows: The number of rows to return
• [Columns]: The number of columns to return.  If excluded, it will return a single column.
• [Start]: The first number in the sequence.  If excluded, it will start at 1.
• [Step]: The amount to increment each subsequent value.  If excluded, each increment will be 1.

Please note, Excel creates sequences across columns, before moving down to the next row.  Look at Example 3 to learn how to flip that around. • Contains 100 Excel VBA macros
• Learn VBA by following along with the example codes
• Apply to your macros, automate Excel, save time. ## Examples of using the SEQUENCE function

The following examples illustrate how to use the SEQUENCE function.

### Example 1 – Basic Usage

The two examples below show the basic usage of the SEQUENCE function.

Excluding optional arguments The formula in cell B2 is:

`=SEQUENCE(8)`

In this formula, only the Rows argument is provided; all the optional arguments have been excluded.  Therefore, the defaults are applied.  SEQUENCE has created a list of 8 rows, 1 column, starting at 1 and incrementing by 1 for each cell.

Including all arguments The formula in cell D2 is:

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

This formula is creating an array which is 8 rows, 3 columns, starting at 5 and incrementing by 10.  The order of the numbers is important here, the function increments across the columns before moving to the next row.

### Example 2 – Using SEQUENCE inside other functions

The example below shows how to use SEQUENCE inside the DATE function.

Note: As I am based in the UK, the screenshot below shows the UK date format (dd/mm/yyyy) The formula in cell B2 is:

`=DATE(2020,SEQUENCE(13),1)`

This formula creates a sequence of monthly dates starting on January 1st 2020.  The SEQUENCE function has been applied to the month argument; therefore, it adds one to the month for each result.  We have requested 13 months, but Excel is intelligent enough to know that 01/13/2020 doesn’t exist as a UK date format.  Instead, the DATE function will cause the result to roll-over into the next year and create 01/01/2021.

### Example 3 – SEQUENCE row / column order

As already mentioned, SEQUENCE increments the numbers across the columns before going down to the next row.  To change this, we can use the TRANSPOSE function. The formula in cell B2 is:

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

This is the same SEQUENCE function as we used in Example 1, but it has been wrapped in TRANSPOSE.  Note that now the numbers increment down each row before going to the next column.

This can get a little confusing as the rows argument is displaying 8 columns and the column argument is displaying 3 rows.  When we use this technique, we just need to remember that TRANSPOSE is applied as the last action.

### Example 4 – Sequence with INDEX

SEQUENCE by itself is useful, but when combined with other functions it’s power can be clearly seen.

In the screenshot below, we have a list of names and then the person’s ID or department.  There are two rows for every individual.  How can we get a list of just the ID’s sorted by last name?  Let’s find out: The formula in cell F2 is:

`=SORT(INDEX(B3:D12,SEQUENCE(COUNTA(B3:B12)/2,1,1,2),SEQUENCE(1,3)),2)`

Let’s break this down:

• SEQUENCE(COUNTA(B3:B12)/2,1,1,2) – this part uses COUNTA to calculate the number of rows, then divides that by 2, as we want to return every 2nd row.  SEQUENCE is then used to create the list which is 1 column, starts at the first row but steps by 2.
• SEQUENCE(1,3) – as we have 3 columns in our source, we want to return the same 3 columns.  This creates the list for INDEX to use.
• INDEX(B3:D12,…..) – The first SEQUENCE function is returning the value {1, 3, 5, 7, 9, 11}, the second SEQUENCE function is returning the values {1;2;3}.  INDEX returns only the values form B3-B12 which are in these row and column positions.
• SORT(….,2) – finally the result is sorted by the 2nd column, which is the last name

This demonstrates the power of SEQUENCE as an enabler to more complex functions.

### Example 5 – SEQUENCE in random order

Do you play bingo?  No, me neither.  But it creates an interesting example.  I’m led to believe there are 75 numbers and those numbers can only appear once.  By combining SORTBY, RANDARRAY and SEQUENCE we can put those numbers into a random order (just like bingo). The formula in cell B2 is:

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

SEQUENCE creates a list from 1 to 75.  This list is sorted by a list of 75 random numbers created by RANDARRAY.

### Other examples

My post about the SORT function also contains an example of using the SEQUENCE function to create a list of the top 5 results.

There is a lot to learn about dynamic arrays and the new functions.  Check out my other posts here to learn more:

Don’t forget: