## Explanation

*Note: In Excel 365, the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions.*

The core of this formula is a string that represents rows. For example, to create an array with 10 numbers, you can hard-code a string into INDIRECT like this:

```
=ROW(INDIRECT("1:10"))
```

The INDIRECT function interprets this text to mean the range 1:10 (10 rows) and the ROW function returns the row number for each row in that range inside an array.

The example shown uses a more generic version of the formula that picks up the start and end numbers from B5 and C5 respectively, so the solution looks like this:

```
=ROW(INDIRECT(B5&":"&C5))
=ROW(INDIRECT(1&":"&5))
=ROW(INDIRECT("1:5"))
=ROW(1:5)
={1;2;3;4;5}
```

The reason INDIRECT is used in the formula is to guard against worksheet changes. Without INDIRECT, inserting or deleting rows can can change the range reference, for example:

```
=ROW(1:5)
```

will change to:

```
=ROW(1:4)
```

If row 1 is deleted. Because INDIRECT works with a reference constructed with text, it isn't effected by changes on the worksheet.

### Relative row numbers in a range

If you need an array that consists of the relative row numbers of a range, you can use a formula like this:

```
=ROW(range)-ROW(range.firstcell)+1
```

See this page for a full explanation.

### Negative values

The ROW function won't handle negative numbers, so you can't mix negative numbers in for **start** and **end**. However, you can apply math operations to the array created by ROW. For example, the following formula will create this array: {-5;-4;-3;-2;-1}

```
=ROW(INDIRECT(1&":"&5))-6
```

### Numbers in reverse order, n to 1

To create an array of positive numbers in descending order, from n to 1, you can use a formula like this:

```
=ABS(ROW(INDIRECT("1:"&n))-(n+1))
```