We can use the ROW and INDIRECT functions to create an array of numbers, e.g. {1; 2; 3; 4}. The functions are mostly used in array formulas requiring a numeric array for processing. This post provides a guide on how to create array of numbers using the ROW and INDIRECT functions.

Figure 1: How to create array of numbers in excel

**Syntax of the formula**

**{=ROW (INDIRECT (start&”:”&end))}**

**How the formula works**

- ROW and INDIRECT functions are fundamental when we want to create an array of numbers
- We need to state the start and end of the array as shown in the example above
- Then in the result column, in put the formula;
`=ROW(INDIRECT(start&”:”&end)))`

- Then press “Enter”

Note that if the column length is not long enough, we only get the start value in the array. To get the array, we create another column; “Array result”. Then, take the formula to formula bar, and click the F9 function to get the array results.

**Understanding the formula**

A string that represents the rows forms the core of this formula. For us to create an array with 5 numbers, we need to hard-core a string into the INDIRECT function. This can be done as below:

**=ROW(INDIRECT(“1:5”))**

Here, the formula works as:

- INDIRECT interprets it to mean range 1:5 (5 rows)
- ROW function returns row number for each row inside the array

**Example**

Figure 2: Creating an array of numbers in excel

In the example above, we proceed as follows:

- Specify the start and end data in the sheet. Also indicate where the result should be as well as the array result.
- In the first row where we have the data, below in the result column, specify the formula, i.e. =ROW(INDIRECT(C2&”:”&D2))
- Press Enter to get the result for the first row.
- Use the F9 key to get the array of the data in the “array result ” column

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment