How to use Microsoft 365’s dynamic array function SEQUENCE() to create a list of dates in Excel

Microsoft 365 updates the way Excel handles dynamic functions. If you’ve ever entered an expression using Ctrl + Shift + Enter, then you’re already familiar with how Excel used to work with dynamic arrays. Thanks to the new dynamic array functions, these types of expressions are much easier to create and maintain. You enter the function as you normally would—with a simple Enter, and the results spill into the cells below, filling as many as necessary to complete the function’s calculations. In this article, I’ll show you how to create a sequence of dates that’s easy to create and update thanks to SEQUENCE().

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. This feature is available only in 365 and Excel Online for now. For your convenience, you can download the demonstration .xlsx file.

About SEQUENCE()

One of 365’s new dynamic array functions is SEQUENCE(), which returns a series of values using the following syntax:

=SEQUENCE(rows,

where rows is required and specifies the number of rows to fill (the number of values to calculate). The optional arguments follow:

• column: the number of columns to return
• start: the first number in the sequence
• step: the amount to increment by

In its simplest form, you could use this function to return a series of fixed values, as shown in Figure A. The results are called the spill range. When using one of the new dynamic array functions, be sure to leave plenty of room below the function for these values. 

Figure A

The only argument, 5, returns five values beginning with 1 and incrementing by 1. Let’s add an input value to make it more flexible. By referencing an input cell (B1), you can automatically change the number of values SEQUENCE() returns, as shown in Figure B. Simply change the input value in B1 to update the results. If you want a descending list, start with 10 and increment by -1 as follows:

=SEQUENCE(B1,,10,-1)

Figure B

How to return a sequence of dates in Excel

Excel’s fill handle can generate a quick list of dates for you, but the list is static; you must create a new list if your needs change. SEQUENCE(), as shown in Figure C, lets you quickly create a truly dynamic list of dates.

Figure C

Let’s take a look at the function’s arguments:

=SEQUENCE(D3-D2+1,,D2,)

The expression D3-D2+1 gives us the number of rows (or values). The difference between Nov.1 and Nov. 5 is 4; add 1 to get five dates: Nov. 1, Nov. 2, Nov. 3, Nov. 4 and Nov. 5. The third argument references D2 and specifies the first value in the sequence. Consequently, the first value in the sequence is the date in D2, and there will be a total of five dates in the sequence. To update the series of dates, change either or both dates in D2:D3.

Figure D shows a similar function that references two input values instead of one. Doing so gives you even more flexibility. In truth, they do the same thing, but in the second example, you don’t have to know the end date. They’re both useful, but you’ll use them in different circumstances.

Figure D

Stay tuned

There are several new dynamic array functions and over the next few months, we’ll cover several of them. Next up, we’ll be using SEQUENCE() to return the top n values in a data set. 

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see