New Dynamic Array Function SEQUENCE

Part One - its all about spilling

Dynamic arrays have the potential to change the way Excel spreadsheets are created. They were released in the January 2020 wave of updates to the Office 365 subscription version.

Note: currently you will only have this new function if you have Office subscription version and the Monthly update channel and you have updated Excel recently.

It is a common requirement to create sequential numbers in Excel.

There are lots of manual ways and a few formula-based ways to do this.

Those techniques will become redundant when you have the SEQUENCE function.

This is a dynamic array function, which means it works differently to Excel‘s standard functions.

Dynamic array functions return multiple results – a range of results.  Let’s see how SEQUENCE works.

In the image below I want to create 6 sequential numbers from 1 to 6 starting in cell A2 going down the sheet.

This is how easy (and dynamic) that is now.

In cell A2 I enter the following formula and press Enter.

=SEQUENCE(D1)

Magic happens – see image below.

There are few things to unpack about what just happened.

  • a range of cells were populated with sequential numbers
  • size of the range was determined by the value in cell D1
  • there is a thin blue outline around the range A2:A7 – this is called the SPILL range – the formula in cell A2 “spills” to the other cells
  • in the bottom of the image, the formula in cell A3 is greyed out – the formula isn’t actually in that cell – the formula is in cell A2 – the top, left cell of the range

If we change cell D1 to another number the “spill” range automatically updates. See image below.

 

The SEQUENCE function has a total of four arguments.

 

It can work across columns – great for month numbers.

 

It can start at a specific number

 

It can increment by a specific value.

 

If the number doesn’t make sense eg cell D1 is blank, you will see the brand new #CALC! error message.

 

If you enter a number negative for the number of entries you see the VALUE error.

 

This is an introduction to dynamic arrays and the SEQUENCE function.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.