How to convert a matrix to a single column using Excel

Question

I have the following matrix in Excel:

3 Columns: A, B, C

Row 1: a b c

Row 2: d e f

Row 3: ghi

What I need is a single column with all these values. The result should look like that:

a

b

c

d

e

f

g

h

i

The TRANSPOSE function doesn't work for that case. I tried out the INDIRECT function, but did not find a solution. I would rather prefer to handle it with standard Excel formulas than with a makro.

Any ideas?

Wore

Possible duplicate of Excel: Formulas for converting data among column / row / matrix – Bethlehem

@Sancho's post mentioned above is required if you're not using Office 365. – Heraclea

Answer 1

Say we have:

In E1 enter:

=INDEX($A$1:$C$3,ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)=0,3,MOD(ROW(),3)))

and copy down to get:

Using similar formulas you can map any two dimensional table into a single row or single column. It is equally easy to map a single column or row into a table.

Katelin

My version of the same approach: =OFFSET($A$1,TRUNC((ROW()-1)/3),MOD(ROW()-1,3)) – Lycanthrope

Found @user3964075 version a little easier to understand. Using the German version of Excel. So it is =BEREICH.VERSCHIEBEN($A$1;KÜRZEN((ZEILE()-1)/3);REST(ZEILE()-1;3)). Might be that even in the English Version the comma must be replaced by an semicolon. Can't test it. If so, please correct. – Wore

I have a rectangular matrix. Which 3 stands for matrix rows and which 3 for matrix columns. – Birkenhead

Hi, can you update the answer with a rectangular matrix? i don't know which 3 is rows and which is columns – Surgical

Answer 2

Place your cursor on the cell where you want to transform. Type below formula. =TOCOL(A1:C3)

That's all.

Baleen

8 years later, with this new function, this is the correct answer. – Boohoo

TOCOL() AND TOROW() are great functions, but they only work in Office 365. See #20801320 as mentioned in the comment off the original post. That's a great solution. – Heraclea

thanks so much for this.. even Gemini and ChatGPT did not have this answer.. LOL – Clarendon

Answer 3

The answers above are quite good, but IMHO, the solution provided by Chip Pearson here (http://www.cpearson.com/excel/TableToColumn.aspx), is superior is most respects since it immediately/automatically:

1) Determines the Row/Col delim values on its own, and immediately works for rectangular, e.g. above one must explicitly enter 3 for num Cols and 3 for num Rows, and must also figure out which is which. Whereas Pearson's solution does this automatically (eg. rmf's comment/concern above).

2) Pearson provides both variants for Col-ordered and also Row-ordered.

Bulletin

Answer 4

I suggest you to check Excel unpivot option to perform your task.

1. Select your matrix
2. Go through the Get & Transform section in the Data tab and click From Table/Range
3. In the new Power Query Editor select the columns you want to unpivot
4. Go through the Any Column section in the Transform tab, click the arrow nearby Unpivot Columns and choose the best option (if you follow the step 3 you can click Unpivot only selected columns)
5. Close & Load

This process is useful especially for complex matrices.
Check the above link for further information

Invite

Answer 5

For a generalized approach that will create an array, you can use:

=LET( Matrix, $A$1:$C$3,
      rM, ROWS( Matrix ),
      cM, COLUMNS( Matrix ),
      cells, SEQUENCE( 1, rM * cM, 0 ),
      INDEX( Matrix, INT( cells / cM ) + 1, MOD( cells, cM ) + 1 )
      )

While this takes advantage of the LET function, it is used for readability. For those not using Excel365, it is possible to do this without LET, but it is just painful to read .

If you need the result to be delivered as a column, change the order of arguments in SEQUENCE

= LET( Matrix, $A$1:$C$3,
      rM, ROWS( Matrix ),
      cM, COLUMNS( Matrix ),
      cells, SEQUENCE( rM * cM, 1 , 0 ),
      INDEX( Matrix, INT( cells / cM ) + 1, MOD( cells, cM ) + 1 )
      ) )

Of course, A1:C3 can be any arbitrarily shaped array.

Gibe

Answer 6

This was already covered in Item 2 from the very general

Excel: Formulas for converting data among column / row / matrix :

The top cell of your target range (say, $H$1) should contain

=INDEX($A$1:$C$3,INT((ROW()-ROW($H$1))/3)+1,MOD(ROW()-ROW($H$1),3)+1)

where $A$1:$C$3 cotains your source data. Copy the formula downwards as needed.

You could also use

=OFFSET($A$1,INT((ROW()-ROW($H$1))/3),MOD(ROW()-ROW($H$1),3))

as metioned in the referred article.

Bethlehem

Answer 7

With the current version of Excel, I realized operations with matrix has improve greatly. For converting a matrix to a single column, I suggest combining INDEX and SEQUENCE Functions in the following way:

Matriz_to_Column_Formula:

=INDEX(matrix_range, 
TRUNC((SEQUENCE(ROWS(matrix_range)*COLUMNS(matrix_range),1,0,1))/COLUMNS(matrix_range)+1,0),
 MOD(SEQUENCE(ROWS(matrix_range)*COLUMNS(matrix_range),1,0,1),COLUMNS(matrix_range))+1)

If you want to remove empty spaces:

=FILTER(Matriz_to_Column_Formula, Matriz_to_Column_Formula<>"")

Here is a short explanation:

Index requires 3 things: matrix source, row and column. The row and column you calculate each time allows index to point at that specific element in your matrix source. Index would give you as many elements as you require, which is Rows*Columns.

Let me expand on Rows and Columns using SEQUENCE to navigate by each element:

Row:

TRUNC((SEQUENCE(ROWS(matrix_range)*COLUMNS(matrix_range),1,0,1))/COLUMNS(matrix_range)+1,0)

The formula SEQUENCE will generate as many elements as your matrix has, starting from 0 until Rows*Columns-1, and the division by columns will point only at a specific row. The +1 is used because sequence starts at 0 and INDEX Row element works from 1 to n. TRUNC is used only to get a integer number of the row.

Column:

MOD(SEQUENCE(ROWS(matrix_range)*COLUMNS(matrix_range),1,0,1),COLUMNS(matrix_range))+1

Here you apply the same principle, using SEQUENCE to generate as many elements as your matrix has from 0 to Rows*Columns-1, and by getting the remainder between the sequence and Columns you will point to the column you need to.

Alright, this is my first post, hope this helps anyone. Thanks for you help in many occasions!

Example in Excel: Matrix to Single Column - Example in excel

Opt 1:

=INDEX(C3:E7,
TRUNC((SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1))/COLUMNS(C3:E7)+1,0),
MOD(SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1),COLUMNS(C3:E7))+1)

Opt 2:

=IF(INDEX(C3:E7,TRUNC((SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1))/COLUMNS(C3:E7)+1,0),MOD(SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1),COLUMNS(C3:E7))+1)="","",INDEX(C3:E7,TRUNC((SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1))/COLUMNS(C3:E7)+1,0),MOD(SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1),COLUMNS(C3:E7))+1))

Remove Empty:

=FILTER(INDEX(C3:E7,TRUNC((SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1))/COLUMNS(C3:E7)+1,0),MOD(SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1),COLUMNS(C3:E7))+1),
INDEX(C3:E7,TRUNC((SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1))/COLUMNS(C3:E7)+1,0),MOD(SEQUENCE(ROWS(C3:E7)*COLUMNS(C3:E7),1,0,1),COLUMNS(C3:E7))+1)<>"")

Eventful