Convert an Excel Matrix to a Column Vector in WPS Spreadsheet
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.

- 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.
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.
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.
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.
Type =TOCOL(A1:C4) in the formula bar, replacing A1:C4 with the actual matrix range.
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 an INDEX, ROWS, and COLUMNS formula for older WPS Spreadsheet or Excel versions
Use this fallback when TOCOL is not available or when you need a formula that can be filled down without copying individual matrix cells.
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. Open WPS Spreadsheet: Download and install WPS Office, then open WPS Spreadsheet from the WPS Office home screen.
- 2. Open the Excel workbook: Click Menu > Open, select the Excel file that contains the matrix, and confirm the worksheet grid appears correctly.
- 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. Save the workbook: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the file to keep Excel compatibility.

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.




