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

3 years ago 401

Microsoft Excel's caller dynamic array functions marque analyzable calculations easier than ever before.

Microsoft 365 updates the mode Excel handles dynamic functions. If you've ever entered an look utilizing Ctrl + Shift + Enter, past you're already acquainted with however Excel utilized to enactment with dynamic arrays. Thanks to the caller dynamic array functions, these types of expressions are overmuch easier to make and maintain. You participate the relation arsenic you usually would—with a elemental Enter, and the results spill into the cells below, filling arsenic galore arsenic indispensable to implicit the function's calculations. In this article, I'll amusement you however to make a series of dates that's casual to make and update acknowledgment to SEQUENCE().

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system. This diagnostic is disposable lone successful 365 and Excel Online for now. For your convenience, you tin download the objection .xlsx file.

About SEQUENCE()

One of 365's caller dynamic array functions is SEQUENCE(), which returns a bid of values utilizing the pursuing syntax:

=SEQUENCE(rows,[columns],[start],[step])

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

column: the fig of columns to return
start: the archetypal fig successful the sequence
step: the magnitude to increment by

In its simplest form, you could usage this relation to instrumentality a bid of fixed values, arsenic shown successful Figure A. The results are called the spill range. When utilizing 1 of the caller dynamic array functions, beryllium definite to permission plentifulness of country beneath the relation for these values.

Figure A

The lone argument, 5, returns 5 values opening with 1 and incrementing by 1. Let's adhd an input worth to marque it much flexible. By referencing an input compartment (B1), you tin automatically alteration the fig of values SEQUENCE() returns, arsenic shown successful Figure B. Simply alteration the input worth successful B1 to update the results. If you privation a descending list, commencement with 10 and increment by -1 arsenic follows:

=SEQUENCE(B1,,10,-1)

Figure B

How to instrumentality a series of dates successful Excel

Excel's capable grip tin make a speedy database of dates for you, but the database is static; you indispensable make a caller database if your needs change. SEQUENCE(), arsenic shown successful Figure C, lets you rapidly make a genuinely dynamic database of dates.

Figure C

Let's instrumentality a look astatine the function's arguments:

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

The look D3-D2+1 gives america the fig of rows (or values). The quality betwixt Nov.1 and Nov. 5 is 4; adhd 1 to get 5 dates: Nov. 1, Nov. 2, Nov. 3, Nov. 4 and Nov. 5. The 3rd statement references D2 and specifies the archetypal worth successful the sequence. Consequently, the archetypal worth successful the series is the day successful D2, and determination volition beryllium a full of 5 dates successful the sequence. To update the bid of dates, alteration either oregon some dates successful D2:D3.

Figure D shows a akin relation that references 2 input values alternatively of one. Doing truthful gives you adjacent much flexibility. In truth, they bash the aforesaid thing, but successful the 2nd example, you don't person to cognize the extremity date. They're some useful, but you'll usage them successful antithetic circumstances.

Figure D

Stay tuned

There are respective caller dynamic array functions and implicit the adjacent fewer months, we'll screen respective of them. Next up, we'll beryllium utilizing SEQUENCE() to instrumentality the apical n values successful a information set.