SEQUENCE function in Excel - auto generate number series

This tutorial demonstrates how to generate number sequences in Excel using formulas, including Roman numerals and random integers, leveraging the dynamic array SEQUENCE function. Manual number sequencing is now obsolete; use Auto Fill for simple sequences or the SEQUENCE function for more complex needs.

• Excel's SEQUENCE Function
• Basic Number Sequence Formula
• Advanced Formula Examples:
  • Creating Decreasing Sequences
  • Vertical Two-Dimensional Sequences
  • Generating Roman Numeral Sequences
  • Generating Ascending/Descending Random Integer Sequences
• SEQUENCE Function Availability

Excel's SEQUENCE Function

The SEQUENCE function generates an array of sequential numbers (e.g., 1, 2, 3...). A dynamic array function in Microsoft Excel 365 and later, its output automatically spills into the required rows and columns.

Syntax:

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

Where:

• rows (optional): Number of rows.
• columns (optional): Number of columns (defaults to 1).
• start (optional): Starting number (defaults to 1).
• step (optional): Increment (positive for ascending, negative for descending, defaults to 1).

The function is only compatible with Excel for Microsoft 365, Excel 2021, and Excel for the web.

Basic Number Sequence Formula

To create a sequential column starting at 1:

SEQUENCE(n) (where n is the number of elements)

For a row:

SEQUENCE(1, n)

Example: =SEQUENCE(10) creates a column of 10 numbers. =SEQUENCE(1,8) creates a row of 8. To fill a 5x3 range: =SEQUENCE(5,3).

To start at 100: =SEQUENCE(5,3,100). To use a step of 10: =SEQUENCE(5,3,100,10).

Key Considerations:

• Requires Microsoft 365 or Excel 2021. Older versions will not support dynamic arrays.
• Ensure sufficient empty cells to accommodate the "spill range" to avoid #SPILL errors.
• The output is either one-dimensional (column or row) or two-dimensional (range).
• Omitted arguments default to 1.

Advanced Formula Examples

The SEQUENCE function's power is amplified when combined with other functions.

Creating Decreasing Sequences

Use a negative step value. For example, =SEQUENCE(10, 1, 10, -1) creates a descending sequence from 10 to 1.

Vertical Two-Dimensional Sequences

By default, SEQUENCE fills horizontally. To force vertical filling, nest it within the TRANSPOSE function:

TRANSPOSE(SEQUENCE(*columns*, *rows*, start, step))

Example: =TRANSPOSE(SEQUENCE(3, 5, 100, 10)) fills a 5x3 range vertically.

Generating Roman Numeral Sequences

Combine SEQUENCE with the ROMAN function: =ROMAN(SEQUENCE(B1, B2, B3, B4)) (where B1-B4 contain the parameters).

Generating Ascending/Descending Random Integer Sequences

Use RANDBETWEEN for the step argument. For increasing random numbers:

=SEQUENCE(B1, B2, B3, RANDBETWEEN(1, 10))

For decreasing:

=SEQUENCE(B1, B2, B3, -RANDBETWEEN(1, 10))

Note: RANDBETWEEN is volatile; results change with worksheet updates. Use "Paste Special" > "Values" to fix the output.

SEQUENCE Function Availability

The SEQUENCE function is only available in Excel for Microsoft 365 and Excel 2021.

Downloadable Practice Workbook

[Link to Excel file (replace with actual link)]

The above is the detailed content of SEQUENCE function in Excel - auto generate number series. For more information, please follow other related articles on the PHP Chinese website!