The Problem (Q-score 6, ranked #143rd of 303 in the Excel VBA archive)
The scenario as originally posted in 2013
Are there formulas to convert data in a column to a matrix or to a row?
And to convert from/to other combinations?
What about an even more complex case: reshape a matrix of width W to width N*W?
There are a few similar or related questions.
I have answered some of them, marked with *.
I keep updating this list, as new similar (or equal) questions are added:
Formating Data: Columns to Rows *
Move content from 1 column to 3 columns *
how to split one column into two columns base on conditions in EXCEL *
writing a macro to transpose 3 columns into 1 row
Excel VBA transpose with characters
Mathematical transpose in excel
How do transform a "matrix"-table to one line for each entry in excel
Convert columns with multiple rows of data to rows with multiple columns in Excel.
How to use VBA to reshape data in excel *
Sorting three columns into six, sorted horizontally by surname using excel *
divide data in one column into more column in excel
Move data from multiple columns into single row *
Some of the answers appear to be “upgradeable” to something more encompassing.
Is that possible?
Sample formats to convert from/to are:
Column
1
2
3
4
5
6
7
...
Row
1 2 3 4 5 6 7 ...
Matrix (with a span of 4 columns here)
1 2 3 4
5 6 7 8
...
Why community consensus is tight on this one
Across 303 Excel VBA entries in the archive, the accepted answer here holds solid answer (above median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — solid answer (above median) (+11)
Advisory answer — community consensus with reference links
Note: the verified answer below is a reference / advisory response rather than a copy-ready snippet.
The idea is to give here something that can likely be used with minor adaptations to the questions listed above, which may also serve as a reference for future related questions.
The essential functions to be used are INDEX or OFFSET. The pros and cons of each one will be given after explicit examples, with reference to the figure. It shows several ranges with their defined names (in italics in the following).
All defined names can be replaced by direct absolute references to the corresponding cells.
1. Column to matrix
The span (in C1) gives the number of columns. Then matrix_data_top_left (D1 here) contains
=INDEX(col_data,(ROW()-ROW(matrix_data_top_left))*span+(COLUMN()-COLUMN(matrix_data_top_left)+1),1)
which is then copied into the rest of matrix_data.
Note that copying also into D5 gives an error, since the resulting formula refers to a cell outside col_data (A1:A16).
The same result is obtained in matrix_data2_top_left (I1) with
=OFFSET(col_data_top,(ROW()-ROW(matrix_data2_top_left))*span+(COLUMN()-COLUMN(matrix_data2_top_left)),0)
and copying similarly into matrix_data2.
Note that copying also into I5 returns 0, not an error.
OFFSET has the advantage of requiring only one cell to be used as a base reference (col_data_top), so extending the source data range with further data does not need redefining the source data range in the formula, one has only to copy-paste into an extended target range.
On the other hand, extending the source data range using INDEX requires first updating it in the formula (changing the range if used explicitly), and then copy-paste into an extended target range. Using a defined name is more versatile for this purpose, as redefining col_data suffices here (and it can be done after extending the target range).
Due to this same property, INDEX provides a kind of automatic bounds checking on the source range, which OFFSET does not.
2. Matrix to column
col_data2_top contains
=INDEX(matrix_data2,INT((ROW()-ROW(col_data2_top))/span)+1,MOD(ROW()-ROW(col_data2_top),span)+1)
and col_data3_top
=OFFSET(matrix_data2_top_left,INT((ROW()-ROW(col_data3_top))/span),MOD(ROW()-ROW(col_data3_top),span))
Both formulas are copied downwards.
The same differences between INDEX and OFFSET exist.
3. Matrix to row
Since OFFSET does not give errors, the remaining formulas will use it. Adapting for INDEX along the lines shown above is easy.
row_data_left contains
=OFFSET(matrix_data_top_left,INT((COLUMN()-COLUMN(row_data_left))/span),MOD(COLUMN()-COLUMN(row_data_left),span))
then copied to the right.
4. Column to row
row_data2_left contains
=OFFSET(col_data_top,COLUMN()-COLUMN(row_data2_left),0)
again copied to the right.
PS: The formula =TRANSPOSE(... works for this case, and it should be entered as an array formula (with ctrl+shift+enter). Nevertheles, it might be desirable to avoid array formulas.
5/6. Row to column/matrix
It is very easy to obtain along these lines.
E.g., col_data_top contains
=OFFSET(row_data_left,0,ROW()-ROW(col_data_top))
and copy down.
7. Matrix transpose
To get in matrix_data3 (not shown in the fig.) the transpose of matrix_data2, one only needs to use matrix_data3_top_left, with the formula
=OFFSET(matrix_data2_top_left,COLUMN()-COLUMN(matrix_data3_top_left),ROW()-ROW(matrix_data3_top_left))
and copied to a suitable target range.
8. Matrix reshape
We want to reshape a matrix into a wider one:
matrix_data4, with N4 rows and M4 columns (width4), into
matrix_data5, with N5=N4/R rows and M5=M4xR columns (width5), with R (rep5) the number of repeats
(matrices not shown in the fig.) Then use
=OFFSET(matrix_data4_top_left,(ROW()-ROW(matrix_data5_top_left))*rep5+INT((COLUMN()-COLUMN(matrix_data5_top_left))/width4),MOD((COLUMN()-COLUMN(matrix_data5_top_left)),width4))
Now we want to reshape a matrix into a narrower one:
matrix_data4, with N4 rows and M4 columns (width4), into
matrix_data6, with N6=N4xS rows and M6=M4/S columns (width6), with S (split6) the number of splits
(matrices not shown in the fig.) Then use
=OFFSET(matrix_data4_top_left,INT((ROW()-ROW(matrix_data6_top_left))/split6),MOD((ROW()-ROW(matrix_data6_top_left)),split6)*width4+(COLUMN()-COLUMN(matrix_data6_top_left)))
When to Use It — classic (2013–2016)
Ranked #143rd in its category — specialized fit
This pattern sits in the 97% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.
What changed between 2013 and 2026
The answer is 13 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.