Sequence

In today’s article, we will take a look at a rarely used function in Excel called SEQUENCE. This function generates a chronological list of numbers within a specified range. Interestingly, the range can be one- or two-dimensional, depending on the rows and columns arguments..

Formula and Explanation

As mentioned above, the purpose of the function is to return a range with a list of numbers in chronological order. Let’s now look at the formula of the function.

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

Where:

  • rows: the number of rows to be returned.
  • columns (optional): the number of columns to be returned.
  • start (optional): the ordinal number from which the sequence should start (default is 1).
  • step (optional): the increment between values (default is 1).

Usage

The SEQUENCE function generates a list of values in one or two dimensions. Confirming the function spills the range into the worksheet according to the specified arguments. The SEQUENCE function can also be used inside another function to create a numeric range, which is often a requirement in more advanced formulas. Let’s look at some examples of using the function:

  • =SEQUENCE(5): the formula will return the range {1;2;3;4;5} in five rows.
  • =SEQUENCE(,5): the formula will return the range {1;2;3;4;5} in five columns.
  • =SEQUENCE(10,5,0,3): the formula will return the range in 10 rows and 5 columns, starting from 0 and increasing it by 3 at each step, as shown in the screenshot below.

The SEQUENCE function works with both positive and negative numbers. Counting down from 10 would look like this: =SEQUENCE(11,1,10, -1), returning the range {10;9;8;7;6;5;4;3;2;1;0}.

Nested in Other Formulas

As dates in Excel are converted to numbers, we can easily use the SEQUENCE function to generate a list of dates. For example, we can create a list of the next 10 days starting from today using the SEQUENCE function along with TODAY.

=SEQUENCE (1,10,TODAY(),1)

We can also use the SEQUENCE function to generate the names of the twelve months (instead of dates) using the TEXT, EDATE, DATE, and SEQUENCE functions.

=TEXT(EDATE(DATE(2022,1,1), SEQUENCE(12,1,0)), “mmmm”)

Summary

In this article, I explained the principles of working with the SEQUENCE function and provided examples of its applications. In future articles, I will delve into more functions that are based on spilling ranges, as working with dynamic ranges allows for the automation of many tasks.

Interesting article? Share further!

Leave A Comment