Returning an Array of Sequential Numbers in Excel

SUMMARY

The following article discusses the use of the ROW and COLUMN functions to generate an array of sequential values.

MORE INFORMATION

Array formulas generally use arrays that represent a series of values. Usually each array can be represented by a range of values on the worksheet and is entered in the form of a reference. If the values in the array are numerous, however, entering the values on the worksheet can be cumbersome and manually entering the array can be tedious.

If the values in the array are sequential in nature, the ROW function is an alternative to manually entering all the values. The ROW function returns the row number of a specified cell reference. When the ROW function is used with a range of cells in an array formula, an array of row numbers is returned. For example, the formula {=ROW(A1:A10)} returns the array {1;2;3;4;5;6;7;8;9;10}. It does not matter what cells A1:A10 contain, and A1:A10 can be substituted with other ranges as long as the range of cells fall within the desired range of row numbers.

Consider the following example. Suppose cell A1 contains the word "EXISTENTIALISM" and you would like to write an array formula that places each character in this word in its own cell down column B. The formula would be entered by highlighting cells B1:B14, typing

   =MID($A$1,ROW(A1:A14),1)

   =MID($A$1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14},1)

This method can also be used to return other series of numbers. Suppose you need an array of numbers that appear as:

   {10;20;30;40;50;60;70;80;90;100}

The ROW function returns a vertical array of numbers. If a horizontal array of numbers is desired, simply use the COLUMN() function and a horizontal range of cells. For example, the formula

   {=COLUMN(K1:T1)}

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 34 and 201

"Microsoft Excel User's Guide," version 3.0, pages 276-289