Exceljet

Quick, clean, and to the point

Create array of numbers

Excel formula: Create array of numbers
Generic formula 
{=ROW(INDIRECT(start&":"&end))}
Explanation 

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.

How this formula works

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

 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.