How to Use the SEQUENCE Function in Excel

Excel's SEQUENCE function: Quickly create a sequence of numbers

Excel's SEQUENCE function can instantly create a series of numeric sequences. It allows you to define the shape of a sequence, the number of values, and the increments between each number, and can be used in conjunction with other Excel functions.

SEQUENCE function is supported only in Excel 365 and Excel 2021 or later.

SEQUENCE function syntax

SEQUENCE function has four parameters:

<code>=SEQUENCE(rows,cols,start,step)</code>

Of:

• rows (Required) Number of rows in which the sequence extends vertically (downward).
• cols (Optional) Number of columns in which the sequence extends horizontally (to the right).
• start (Optional) The starting number of the sequence.
• step (Optional) Increment between each value in the sequence.

The

rows and cols parameters (size of the result array) must be integers (or formulas for outputting integers), while the start and step parameters (the starting number and increment of the sequence) can be integers or decimal. If the step parameter is 0, the result will repeat the same number because you tell Excel not to add any increments between each value in the array.

If you choose to omit any optional parameters (cols, start or step), they will default to 1. For example, enter:

<code>=SEQUENCE(2,,10,3)</code>

will return a sequence with only one column, because the cols parameter is missing.

SEQUENCE is a dynamic array formula, which means it can generate overflow arrays. In other words, although the formula is entered into only one cell, if the rows or cols parameters are greater than 1, the result will overflow to multiple cells.

How the SEQUENCE function works

Before showing some variations and practical applications of the SEQUENCE function, here is a simple example to demonstrate how it works.

In cell A1, I typed:

<code>=SEQUENCE(3,5,10,5)</code>

This means that the sequence has three rows in height and five columns in width. The sequence begins with the number 10, with each subsequent number increasing by 5 from the previous number.

Fill down first and then right: TRANSPOSE function

In the example above, you can see that the sequence first fills the columns horizontally and then fills the rows downwards. However, by embedding the SEQUENCE function into the TRANSPOSE function, you can force Excel to fill the rows downward and then horizontally.

Here, I entered the same formula as the above example, but I also embed it into the TRANSPOSE function.

<code>=TRANSPOSE(SEQUENCE(3,5,10,5))</code>

As a result, Excel reverses the rows and cols parameters in the syntax, which means that "3" now represents the number of columns and "5" now represents the number of rows. You can also see the numbers fill down first and then right.

Create a Roman numeral sequence

If you want to create a sequence of Roman numerals (I, II, III, IV) instead of Arabic numerals (1, 2, 3, 4), you need to embed the SEQUENCE formula into the ROMAN function.

Using the same parameters as the above example, I typed in cell A1:

<code>=SEQUENCE(rows,cols,start,step)</code>

Produces the following results:

Go further, suppose I want the Roman numerals to be lowercase. In this case, I would embed the entire formula into the LOWER function.

<code>=SEQUENCE(2,,10,3)</code>

Create date using SEQUENCE function

A more practical use of the

SEQUENCE function is to generate a series of dates. In the example below, I want to create a report with each person’s weekly profit starting on Friday, March 1 and lasting for 20 weeks each Friday.

To do this, I type in cell B2:

<code>=SEQUENCE(3,5,10,5)</code>

Because I want the date to span the previous row and 20 columns, starting on Friday, March 1, each value is incremented by 7 days.

Before adding dates to cells, especially when creating dates with formulas, you should first change the number format of the cell to Date in the Numbers group on the Start tab of the ribbon. . Otherwise, Excel may return the serial number instead of the date.

Make the SEQUENCE function depend on another parameter

In this example, I have a series of tasks that need to be numbered. I want Excel to automatically add another number when I add a new task (or, likewise, delete a number when I complete and delete the task).

To do this, I type in cell A2:

<code>=TRANSPOSE(SEQUENCE(3,5,10,5))</code>

The number of rows filled in the sequence now depends on the number of cells containing the text in column B (thanks to the COUNTA function), I added "-1" at the end of the formula so that COUNTA calculation ignores the title row.

You will also notice that I only specified the rows parameters (number of rows) in the SEQUENCE formula, because omitting all other parameters will default to 1, which is exactly what I want in this example. In other words, I want the result to occupy only one column, the number starts at 1 and increment by 1 each time.

Now, when I add an item to the list of column B, the number in column A is automatically updated.

Things to note when using SEQUENCE function

When using SEQUENCE function in Excel, you need to pay attention to the following three precautions:

1. Dynamic array formulas that generate overflow arrays (including SEQUENCE) cannot be used in formatted Excel tables. If you want to use SEQUENCE in your existing data, the best solution is to format it by selecting a cell in the table and clicking Convert to Area in the Tools group on the Table Design tab Convert Excel tables to non-formatted areas.
2. If you create a dynamic array linking two workbooks, this will only work if both workbooks are open. Once you close the source workbook, the dynamic array formula in the active workbook will return a #REF! error.
3. Breaking the overflow array by placing another value in the affected cell will destroy your SEQUENCE function and cause a #SPILL! error.

Why use the SEQUENCE function instead of the padding handle?

The alternative to the

The SEQUENCE function is the fill handle of Excel, which you can click and drag to continue the sequence you have already started:

However, I prefer to use the SEQUENCE function rather than the fill handle for several reasons:

• If you want to create a long sequence, dragging will take a long time!
• It is easier to modify the parameters of a sequence in the SEQUENCE function - just adjust the parameters in the formula. When you click and drag the fill handle, you must remember to select multiple numbers in the existing array.
• If you delete rows or columns that interact with the sequence, the numbers created by the padding handle will also be deleted. However, since SEQUENCE produces overflow arrays, they remain in place even if you refactor the spreadsheet.
• Excel's fill handle is designed to fill a sequence along a single row or a single column. To create a sequence covering multiple rows and columns with a padding handle, you need to take a few more steps than using the SEQUENCE function, which allows you to specify all parameters at once.
The
• SEQUENCE function eliminates human errors that may occur when using fill handles.

---

If you use SEQUENCE with volatile functions such as DATE, this may cause your Excel workbook to be significantly slower, especially if you already have a lot of data in your spreadsheet. Therefore, try to limit the number of volatile functions you use to ensure your Excel tables work quickly and efficiently.

The above is the detailed content of How to Use the SEQUENCE Function in Excel. For more information, please follow other related articles on the PHP Chinese website!