Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get Expert help in seconds.

Your message must be at least 40 characters
Our professional Expert are available now. Your privacy is guaranteed.

# Create Array of Numbers

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.

### Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
in range b2:o2, I am trying to write a formula to count if the values are equal to 1 thru 27.
Solved by E. L. in 52 mins
I have a google sheet that takes information from a google form, that creates a row every time a form is submitted. Since it is a new row that is created, rather than filling in the next row down, the formulas I have applied to some of the columns do not work. The same is true when I try and link the data to another sheet to bypass this problem. This is probably quite a general functionality issue so I won't send the actual document but let me know if you need it
Solved by F. F. in 13 mins
Repost I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula.
Solved by S. H. in 9 mins
I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula.
Solved by E. S. in 60 mins
I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula
Solved by V. H. in 60 mins