Function Problems

Convert an Excel Matrix to a Column Vector in WPS Spreadsheet

Steven ParkerSteven Parker Nov 13, 2026 868 views

Question details

The user needs to flatten an Excel matrix into a single column in row-major order, reading each row left to right before moving to the next row.

Convert an Excel Matrix to a Column Vector in WPS Spreadsheet
Product
WPS Spreadsheet / Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains a matrix, and the user wants to reshape it into a single continuous column for analysis, import, or further formulas.
Observed behavior
Manual copying is slow and error-prone; dragging each matrix cell into a column does not automatically maintain row-major order.
Before you start

Check the matrix range and choose an empty output column with enough cells for the result. If you plan to use TOCOL, make sure your WPS Office version supports dynamic array functions.

Solution 1Recommended

Use the TOCOL function to spill a matrix into one column in row order

Best when your version of WPS Spreadsheet supports dynamic arrays and you want a single formula that updates automatically.

TOCOL scans the matrix from left to right across the first row, then the next row, and returns all values in one dynamic column. This removes the need to copy or drag individual cells.

1
Select the output cell

Click the cell where the single-column output should start, such as E1, and make sure the cells below are empty for the spill range.

2
Enter the TOCOL formula

Type =TOCOL(A1:C4) in the formula bar, replacing A1:C4 with the actual matrix range.

3
Press Enter and verify the spill

Press Enter. WPS Spreadsheet spills the matrix into a single column in row-major order and updates the result when the source matrix changes.

Use the TOCOL function to spill a matrix into one column in row order
Dynamic spill result: TOCOL returns all matrix values automatically, so no fill handle or manual copying is required.
WPS Spreadsheet formula solution

Flatten an Excel matrix with WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use TOCOL or the INDEX fallback to turn a matrix into one column without dragging cells manually.

  1. 1. Open WPS Spreadsheet: Download and install WPS Office, then open WPS Spreadsheet from the WPS Office home screen.
  2. 2. Open the Excel workbook: Click Menu > Open, select the Excel file that contains the matrix, and confirm the worksheet grid appears correctly.
  3. 3. Apply the matrix-to-column formula: Click the output cell, type =TOCOL(A1:C4) or the INDEX fallback formula with your matrix range, and press Enter.
  4. 4. Save the workbook: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the file to keep Excel compatibility.
Direct matrix-to-column conversion with a single formulaDynamic spill updates with TOCOL on supported WPS versionsFull compatibility with Microsoft Excel XLSX files and formulasFree, lightweight spreadsheet interface that is familiar to Excel users
microsoft office alternative - wps office

Frequently Asked Questions

Does WPS Spreadsheet support TOCOL?

TOCOL is supported in WPS Office versions that include dynamic array functions. If it is not available, use the INDEX-based fallback formula shown in the second solution.

How do I make TOCOL read columns first instead of rows?

Use the third argument of TOCOL: =TOCOL(A1:C4,0,TRUE). The TRUE value enables scan-by-column, while the default FALSE returns row-major order.

How can I skip empty cells when flattening a matrix?

Set the second argument of TOCOL to 1, for example =TOCOL(A1:C4,1), to ignore blanks and return only non-empty cells.

Will the output update automatically when the matrix changes?

Yes, when you use TOCOL, the spilled column recalculates automatically. With the INDEX fallback, the filled formula cells also recalculate as long as the output range covers all source cells.