Use Sequence() to get an ordered list of numbers either increasing or decreasing and with your choice of increments. Sequence is possibly the least understood of the dynamic array functions but combined with other functions it greatly extends Excel’s power.
Sortby(), Filter(), Unique() and RandArray() all have obvious uses but Sequence() seems almost pointless. Here’s a simple Sequence(4,3) result,
Sequence drops a series of integers into the array (e.g 4 rows x 3 columns). So what? On it’s own very little but added into other formulas there’s a lot possible with Sequence.
It’s a replacement for the current drag selection method of extending a list of values down or across a sheet.
Sequence() options
Sequence() has four options, they all default to 1 if not specified.
Rows – the number of rows to fill
Columns – how many columns to fill
Start – the number to start the sequence
Step – how many to increment, can be negative to go back from the Start value.
For example Sequence(3,4,5,2.5) has 3 rows and 4 columns starting at 5 and increasing 2.5 each time.
Sequence in a column or row only
To make a sequence of numbers in a column just use the first parameter Sequence(7)
For a row across only, enter the number of cells into the second parameter Sequence(1,7)
Sequence down columns then across rows
The default for a two-dimensional Sequence() is to count across rows then work down, see the first example in this article. What if you want to count down columns?
That’s where Transpose comes in. Transpose() switches rows to columns and vice-versa.
Descending sequence
Reverse numbering is too easy, just use a negative Step value e.g. Sequence(10,1,100,-1)
Random changes to increasing or decreasing sequence
Make a list of values with a random step value each time the formula is calculated.
=Sequence(10,1,1,Rand()*10) or =Sequence(10,1,1,RandBetween(1,10)
The difference between each number is the same (in this example 7) but that changes each time the formula is refreshed. In other words, RandBetween() is only calculated once when the formula updated.
Sequence as text
Any Sequence() can be made into part of a text string. Add (concatenate) the text either before or after the Sequence().
="ID-" & SEQUENCE(10,1,100,1) & "-X"
Dynamic Arrays now in Excel for iPhone and iPad
Great and simple ‘live’ sorting with filtering in Excel
Better live sorting in Excel 365
Easy and better lists with Excel 365’s Filter()
Unique() makes a once difficult task really easy in Excel
Extend UNIQUE() for distinct values that appear more than once
RandArray – Excel’s new way to get random numbers
