Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to convert matrix to vector or single column in Excel?

Have you ever tried to convert a matrix of cells to a single row or column in Excel? Maybe, you can copy a row or a column and paste them one by one to a single vector, but, this will be time-consuming if there are multiple rows and columns. In this article, I will talk about some interesting methods and hope them help you.

Convert a matrix of cells to single row or column with formulas

Convert a matrix of cells to single row or column with Kutools for Excel


Convert multiple columns or rows to one single column or row:

Kutools for Excel's Transform Range utiltiy can help you to convert a range of cells to only one column or row, on the contrary, it also can convert a single row or column to a range of cells.

doc convert matrix to column 09

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!



The following formulas may help you to quickly convert the matrix to a single row or column. Please do as follows:

Convert a matrix of cells to a single column

1. First of all, you should define a range name for the matrix data. Select the range of cells and type a range name in the Name box which is next to the formula bar, and then press Enter key. In this case, I type “Matrix” as the defined name, see screenshot:

doc convert matrix to column 1

2. Then enter the following formulas:

(1.) Convert the matrix to single column based on row, it means the values are taken from each row, moving across then down: =OFFSET(Matrix,TRUNC((ROW()-ROW($G$1))/COLUMNS(Matrix)),MOD(ROW()-ROW($G$1),COLUMNS(Matrix)),1,1)(Matrix is the range name you defined in step 1, and G1 is the cell you enter this formula). Then drag the fill handle to the cells till 0 is displayed, see screenshot:

doc convert matrix to column 2

(2.) Convert the matrix to single column based on column, it means the values are taken from columns, moving down one column and then across to the right column: =OFFSET(Matrix,MOD(ROW()-ROW($G$1),ROWS(Matrix)),TRUNC((ROW()-ROW($G$1))/ROWS(Matrix)),1,1) (Matrix is the range name you defined in step 1, and G1 is the cell you enter this formula). Then drag the fill handle to the cells till 0 is displayed, see screenshot:

doc convert matrix to column 3

Convert a matrix of cells to a single row

To convert a matrix of cells to a single row, you can use the following formulas:

(1.) Convert the matrix to single row based on row, it means the values are taken from each row from left to right on one row and then moving down to the next row: =OFFSET(Matrix,TRUNC((COLUMN()-COLUMN($A$7))/COLUMNS(Matrix)),MOD((COLUMN()-COLUMN($A$7)),COLUMNS(Matrix)),1,1) (Matrix is the range name you created for your data range, and A7 is the cell you enter this formula). Then drag the fill handle right to the cells till 0 is displayed, see screenshot:

doc convert matrix to column 4

(2.) Convert the matrix to single row based on column, it means the values are taken from each column, moving down one column and then across to the right column: =OFFSET(Matrix,MOD((COLUMN()-COLUMN($A$7)),ROWS(Matrix)),TRUNC((COLUMN()-COLUMN($A$7))/(ROWS(Matrix))),1,1) (Matrix is the range name you created for your data range, and A7 is the cell you enter this formula). Then drag the fill handle right to the cells till 0 is displayed, see screenshot:

doc convert matrix to column 5


If the above formulas are too long to remember, here, I can recommend an easy and powerful tool – Kutools for Excel, with its Transform Range utility, you can quickly convert multiple columns and rows to a single column or row as you need.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

After installing Kutools for Excel, please do as following steps:

1. Select the matrix of cells that you want to convert.

2. Then click Kutools > Range > Transform Range, see screenshot:

doc convert matrix to column 06

3. In the Transform Range dialog, select Range to single column if you want to convert the matrix to a single column, or choose Range to single row if you want to convert matrix to a single row, see screenshot:

doc convert matrix to column 07

4. Then click OK button, and in the popped out Transform Range box, select a cell where you want to put the result, see screenshot:

doc convert matrix to column 08

5. And then click OK button, your select matrix of cells have been converted a single row or column.

To know more about this Transform Range utility.

 Download and free trial Kutools for Excel Now !


Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jon · 1 years ago
    Fantastic explanation - thank you. This helped me save hours of time
  • To post as a guest, your comment is unpublished.
    Kevin H · 1 years ago
    I'd like to accomplish this same task but with a range that is not so neatly defined. I've got wholes in the data and my matrix is spaced out over my spreadsheet rather than in a neatly define A1:D4 style block. Any ideas on how I can accomplish this?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Kevin:

      The following VBA code can help you to combine the cells from a range:

      Sub a()
      Dim xSRg As Range
      Dim xDRg As Range
      Dim I As Long
      Dim J As Long
      Dim K As Long
      Dim xArr
      On Error Resume Next
      Set xSRg = Application.InputBox("Please select the data range:", "KuTools for Excel", Selection.Address, , , , , 8)
      If xSRg Is Nothing Then Exit Sub
      Set xDRg = Application.InputBox("Select an output cell:)", "KuTools for Excel", , , , , , 8)
      If xDRg Is Nothing Then Exit Sub
      xArr = xSRg
      K = 0
      For I = 1 To UBound(xArr, 1)
      For J = 1 To UBound(xArr, 2)
      If xArr(I, J) <> "" Then
      xDRg.Offset(K, 0).Value = xArr(I, J)
      K = K + 1
      End If
      Next
      Next
      End Sub

      Please try it, Hope it can help you!
    • To post as a guest, your comment is unpublished.
      Ally · 1 years ago
      I had the same issue, put a filter on that column, and anything that has 0, filter out.
  • To post as a guest, your comment is unpublished.
    Gr8NW · 1 years ago
    This is so useful! The formula to convert a matrix to single column based on column was exactly what I needed. Thank you!