Summary

To create an array of numbers like {1;2;3;4;5} you can use a formula based on the ROW and INDIRECT functions. This technique is most often used in array formulas that need a numeric array for processing of some kind. In the example shown, the formula in D5 is:

{=ROW(INDIRECT(B5&":"&C5))}

which returns an array like {1;2;3;4;5}.

Note: when entered in a single cell, Excel will display only the first item in the array. Use F9 in the formula bar to see the actual array result. Normally, you will use this formula inside a larger array formula, entered with control + shift + enter.

Generic formula

{=ROW(INDIRECT(start&":"&end))}

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

 

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.