Often, it is useful to create a single column or row of data from a MxN range of data.   For example, when charting you cannot assign a rectangular range to a single data series.  The series must be in a single column or row of data.   This problem came up with a user who had an array of measurement values, with 31 columns (day of month) and 24 rows (hour of day).  He wanted to create an XY-Scatter chart, displaying the measurement values.   Since the y-axis data (measurement values) had to be a single column of data, we had to convert the array to a column.

The following formulas assume that the data is in a range named "MyRange" with M rows and N columns.

To create a column of data containing your original data in row-by-row format, use the following formula.  Row-by-row means that the first N entries contain the first row of "MyRange", the next N entries contain the second row of "MyRange", and so on.    Name the range that will contain the data "CxRV".

=INDIRECT(ADDRESS( ROW(MyRange)+INT((ROW()-ROW(CxRV))/COLUMNS(MyRange)),
COLUMN(MyRange)+MOD(ROW()-ROW(CxRV),COLUMNS(MyRange)),4,,"Sheet1"))

Enter this formula in the first cell of the range CxRV, and use Fill Down to fill the entire range.

To create a column of data containing your original data in column-by-column format, use the following formula.  Column-by-column means that the first M entries contain the the first column of "MyRange", the next M entries contain the second column of "MyRange", and so on.  Name the range that will contain the data "CxCV".

=INDIRECT(ADDRESS(ROW(MyRange)+MOD(ROW()-ROW(CxCV),ROWS(MyRange)),
COLUMN(MyRange)+INT((ROW()-ROW(CxCV))/ROWS(MyRange)), 4,,"Sheet1"))

Enter this formula in the first cell of the range CxCV, and use Fill Down to fill the entire range.

To create a row of data containing your original data in row-by-row format, use the following formula.  Row-by-row means that the first N entries contain the first row of "MyRange", the next N entries contain the second row of "MyRange", and so on.    Name the range that will contain the data "RxRV".

=INDIRECT(ADDRESS(ROW(MyRange)+INT((COLUMN()-COLUMN(RxRV))/
COLUMNS(MyRange)),COLUMN(MyRange)+MOD(COLUMN()-COLUMN(RxRV),
COLUMNS(MyRange)), 4,,"Sheet1"))

Enter this formula in the first cell of the range RxRV, and use Fill Right to fill the entire range.

To create a row of data containing your original data in column-by-column format, use the following formula.  Column-by-column means that the first M entries contain the the first column of "MyRange", the next M entries contain the second column of "MyRange", and so on.  Name the range that will contain the data "RxCV".

=INDIRECT(ADDRESS(ROW(MyRange)+MOD(COLUMN()-COLUMN(RxCV),ROWS(MyRange)),
COLUMN(MyRange)+INT((COLUMN()-COLUMN(RxCV))/ROWS(MyRange)),4,,"Sheet1"))

Enter this formula in the first cell of the range RxCV, and use Fill Right to fill the entire range.

In all of the formulas listed, be sure to change "Sheet1" to the name of worksheet containing MyRange.

Note that if a cell is in MyRange is blank, it will appear as a zero in a cell containing these formulas.   This is a result of the =INDIRECT function.  You can get around this by using an =IF statement:

=IF(INDIRECT(...)="","",INDIRECT(...) )

Since these formulas are long, you may find it useful to assign the =INDIRECT(...) formula to a defined name, and then enter either

=MyName   or
=IF(MyName = "", "", MyName)

in the cells.

These formulas have the advantage over a macro-based solution because whenever data is altered in MyRange, the changes are automatically reflected in the column or row range, without further user intervention.

Download a worksheet illustrating these formulas.