Convert Rows to a Single Column in Excel

How to merge two columns into one alternating column. Or, convert multiple rows into one column. I have a couple videos that already address this issue, but here's another way to attack the problem.

Below is the formula, but watch the video for quick instructions and to make it worthwhile to create these videos :)

=INDEX(Data,1+INT((ROW(A1)-1)/COLUMNS(Data)),MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1)

Below is a description of each part of the formula:

1. **INDEX(Data,...)**: This function returns a value or the reference to a value from within a given range (which is "Data" in this case). It has two parts, the row number and the column number.

2. **1+INT((ROW(A1)-1)/COLUMNS(Data))**: This calculates the row number for the INDEX function:
A. **ROW(A1)**: This function returns the row number of cell A1, which is 1.
B. **(ROW(A1)-1)/COLUMNS(Data)**: This subtracts 1 from the row number of A1 and divides the result by the total number of columns in the range "Data".
C. **INT((ROW(A1)-1)/COLUMNS(Data))**: The INT function rounds down the result of the division to the nearest integer.
D. **1+INT((ROW(A1)-1)/COLUMNS(Data))**: Then, 1 is added to the result to calculate the row number for the INDEX function.

3. **MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1**: This calculates the column number for the INDEX function:
A. **ROW(A1)-1+COLUMNS(Data)**: Subtract 1 from the row number of cell A1 and add the total number of columns in the range "Data".
B. **MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))**: The MOD function returns the remainder of the first part divided by the total number of columns in the range "Data".
C. **MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1**: Then, 1 is added to the result to calculate the column number for the INDEX function.

This formula is a way of returning the elements of a two-dimensional range ("Data") in a single column or row. For example, if "Data" is a 2x2 range, this formula would return the elements in the following order: top left, top right, bottom left, bottom right.

#excel #exceltutorial #msexcel

[TheQueb]

Worked perfectly! Is there a way to have it skip cells with no data?

[djvandy]

Thanks for the help, dude! This was a great alternative for a more complex index that I was trying to do with concatenated values and having zero luck.

[BeaverMonkey]

Clear and concise! Another great video

[LumpyLarper]

Thank you, this was great! How would I do this transpose with some columns, while leaving others static? I need columns A - J to remain as 1 row, while transposing K - AD.

[jabbarshaheed]

Thank you for this effort. Wishing you continued success.

[randomperson]

thank you for this!! it was SUPER helpful to work through some data that I needed to plot (original data output was 100+ rows across 10 columns)!

It will be really helpful if you can maybe add some descriptors to the formula below to explain what part means what? Will be useful to learn a bit more in addition to blatantly copy/pasting your genius formula :D Thanks again!!

[Shreyasbj]

how to avoid the zero entry in the column if there is no value for reference

[antoanetahop]

I tried to do it with a big file of emails on many rows and columns, but it just gives me True/False response, does this formula work only with numbers and not text?