Quick Links
Excel’s SEQUENCE function lets you create a list of numbers in a sequence in an instant. It also allows you to define the shape, number of values, and steps between each number in the sequence, and you can use SEQUENCE in conjunction with other Excel functions.
The SEQUENCE function is only supported in Excel 365 and Excel 2021 or later.
where
Argumentsaandb(the size of the resultant array) must be whole numbers (or formulas that output a whole number), while argumentscandd(the starting number and the increment in the sequence) can be either whole or decimal numbers. If argumentdis 0, the result will repeat the same number, as you’re telling Excel not to add any increments between each value in the array.
If you choose to omit any of the optional arguments (b,c, ord), they will default to 1. For example, typing
will return a sequence that is one column in height, since argumentbis missing.
SEQUENCE is a dynamic array formula, meaning it can producea spilled array. In other words, although the formula is typed into one cell, if argumentsaorbare greater than 1, the result will spill over to more than one cell.
How the SEQUENCE Function Works
Before I show you some variations and real-world uses of SEQUENCE, here’s a straightforward example that demonstrates how it works.
In cell A1, I typed
meaning the sequence is three rows in height and five columns wide. The sequence starts with the number 10, and each subsequent number is an increment of five from the previous one.
Filling Down Then Across: TRANSPOSE
In the example above, you can see that the sequence first fills across the columns, and then down the rows. However, by embedding the SEQUENCE function withinthe TRANSPOSE function, you can force Excel to fill the numbers down the rows, and then across the columns.
Here, I typed the same formula as in the example above, but I also embedded it within TRANSPOSE.
As a result, Excel flipped argumentsaandbin the syntax, meaning the “3” now represents the number of columns, and the “5” represents the number of rows. You can also see that the numbers fill down and then across.
Creating a Sequence of Roman Numerals
If you want to create a sequence ofRoman numerals (I, II, III, IV)instead of Arabic numbers (1, 2, 3, 4), you’ll need to embed your SEQUENCE formula within the ROMAN function.
Using the same parameters as in the example above, I typed
into cell A1 to produce this result:
Going one step further, suppose I wanted the Roman numerals to be lowercase. In this scenario, I would embed the whole formula withinthe LOWER function.
Using SEQUENCE to Create Dates
A more practical use of the SEQUENCE function is to generatea sequence of dates. In the example below, I wanted to create a report that contained each person’s weekly profit, starting on Friday, March 1st, and continuing each Friday for 20 weeks.
To do this, I typed
into B2, because I wanted the dates to run across the first row for 20 columns, starting Friday, March 1st, and incrementing seven days from one value to the next.
Before you add a date to a cell, especially if created using a formula, you should firstchange the cell’s number format to “Date"in the Number group of the Home tab on the ribbon. Otherwise, Excel may return a serial number rather than a date.
Making SEQUENCE Depend on Another Parameter
In this example, I have a list of tasks that need to be numbered. I want Excel to automatically add another number when I add another task (or, by the same token, remove a number when I complete and delete a task).
To do this, in cell A2, I typed
The number of rows the sequence fills now depends on the number of cells in column B that contain text (thanks tothe COUNTA function), and I added “-1” to the end of the formula so that the COUNTA calculation discounts the heading row.
You’ll also notice that I only specified argumenta(the number of rows) within my SEQUENCE formula, because leaving all the other arguments out defaults them to 1, which is what I want in this example. In other words, I want the result to only occupy one column, the numbering to start at 1, and the sequence to increase by one each time.
Now, when I add an item to the list in column B, the numbering in column A updates automatically.
Things to Note When Using SEQUENCE
There are three caveats to be aware of when using the SEQUENCE function in Excel:
An alternative to the SEQUENCE function isExcel’s fill handle, which you can click and drag to continue a sequence you have already started:
However, there are several reasons why I prefer to use the SEQUENCE function instead:
If you use SEQUENCE witha volatile function, such as DATE, this could cause your Excel workbook to slow considerably, especially if you already have lots of data in your spreadsheet. So, try to limit the number of volatile functions you use to ensure yourExcel sheet works quickly and effectively.
