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