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.