Skip to main content

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


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 30 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:

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 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

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!
Comments (7)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks much! Stacking a 2D matrix of data as a 1D column -> That was cool! Solved my problem precisely. Though I had to tweak the formula to get it right (I think the row and col are interchanged)... But the inspiration was from this article. Simply superb! Thanks!
This comment was minimized by the moderator on the site
Fantastic explanation - thank you. This helped me save hours of time
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
Thank you!!!!
This comment was minimized by the moderator on the site
I had the same issue, put a filter on that column, and anything that has 0, filter out.
This comment was minimized by the moderator on the site
This is so useful! The formula to convert a matrix to single column based on column was exactly what I needed. Thank you!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations