The SEQUENCE function in Excel is a powerful tool for generating a sequence of numbers in an array, whether it's a single column, a single row, or even a grid of rows and columns. This function can simplify tasks that require a series of numbers, such as generating unique identifiers, dates, or any ordered data.
Syntax
The syntax for the SEQUENCE function is as follows:
| Argument | Description |
|---|---|
| =SEQUENCE(rows, [columns], [start], [step]) | Generates an array of sequential numbers. |
rows: The number of rows to fill with the sequence.
columns: (Optional) The number of columns to fill with the sequence. Defaults to 1.
start: (Optional) The starting number for the sequence. Defaults to 1.
step: (Optional) The amount by which each value increases. Defaults to 1.
Example
Let's explore a more complex example to demonstrate the versatility of the SEQUENCE function. Suppose you want to create a table showing a sequence of dates for a project timeline, where each task lasts five days.
| Task | Start Date | End Date |
|---|---|---|
| Task 1 | 01/01/2024 | 05/01/2024 |
| Task 2 | 06/01/2024 | 10/01/2024 |
| Task 3 | 11/01/2024 | 15/01/2024 |
| Task 4 | 16/01/2024 | 20/01/2024 |
In this example, you can use the SEQUENCE function to generate the start dates automatically by specifying the number of rows (tasks) and the step (5 days). The end dates can be calculated by adding 4 to each start date.
=SEQUENCE(4, 2, DATE(2024, 1, 1), 5)Practice Exercise 1
Task: Generate a sequence of even numbers from 2 to 20 across two rows and five columns.
Practice Exercise 2
Task: Create a sequence of dates starting from 01/01/2024, filling a table with four rows and three columns, with each subsequent date incremented by 3 days.
Solution for Exercise 1:
To generate a sequence of even numbers from 2 to 20 across two rows and five columns, use the following formula:
=SEQUENCE(2, 5, 2, 2)This formula creates a sequence of numbers starting at 2, with each subsequent number increased by 2, filling two rows and five columns.
Solution for Exercise 2:
To create a sequence of dates starting from 01/01/2024 and incremented by 3 days, filling four rows and three columns, use the following formula:
=SEQUENCE(4, 3, DATE(2024,1,1), 3)Of course, dates have to be formatted (Right Click >> Format Cells >> Dates).
This formula starts from the specified date (01/01/2024) and increments each subsequent date by 3 days, filling the specified grid.