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!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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 · 2 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!