Create array of numbers

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.

April 14, 2025 · 1 min · 112 words · Ariel Williams

Table of Contents

The method explained below will work in previous versions.

The core of this formula is a string that represents rows.

Because INDIRECT works with a reference constructed with text, it isn’t affected by changes on the worksheet.

Excel formula: Get relative row numbers in range

However, you could apply math operations to the array created by ROW.

INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.

ROW Function

The Excel ROW function returns the row number for a reference.

Excel formula: Get relative column numbers in range

For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.

When no reference is provided, ROW returns the row number of the cell which contains the formula.

Excel formula: Sum top n values

Excel formula: Return array with INDEX function

Excel INDIRECT function

Excel ROW function