Create array of numbers

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))