How to convert vector / single row or column to matrix in Excel?
In Excel, you may encounter situations where you need to transform a single row or column—often called a "vector"—into a matrix or table format, with a specified number of rows and columns. Manually copying and pasting each value into a grid can be tedious and error-prone, especially with large data sets. To help you improve efficiency and reduce the risk of mistakes, this article introduces several practical methods to quickly convert a vector (a single row or column) into a matrix. Each method is suitable for different scenarios, so you can choose the one that best fits your needs for data transformation and presentation.
➤ Convert vector / single row or column to matrix with formulas
➤ Convert vector / single row or column to matrix with Kutools for Excel
➤ Convert vector / single row or column to matrix using VBA Code
Convert vector / single row or column to matrix with formulas
Excel formulas offer a flexible and dynamic way to convert a single row or column into a matrix layout. This is especially useful when you want the matrix to update automatically if the source data changes. However, be mindful of cell references and matrix sizing—blank cells in the original data may return zeros.
🔹 Convert a single column to a matrix:
Suppose your data is in cells C1:C20, and you want to convert it into a 4-row by 5-column (4×5) matrix. Here's how:
Step 1: In the top-left cell where you want the matrix to begin (e.g., F1), enter the following formula, and press Enter.
=OFFSET($C$1:$C$20,COLUMN()-COLUMN($F$1)+((ROW()-ROW($F$1))*(ROWS($C$1:$C$20)/4)),0,1,1)This formula will reference and rearrange the column data into a matrix format.
Formula explanation:
- $C$1:$C$20: The source column data.
- $F$1: The matrix's top-left starting cell.
- ROWS($C$1:$C$20)/4: "4" is the number of matrix rows. Adjust this as needed.
Step 2: Drag the fill handle down for 4 rows, then to the right for 5 columns to complete the matrix.
🔹 Convert a single row to a matrix:
To convert a row like A1:T1 into a 5-row by 4-column (5×4) matrix:
Step 1: In your starting cell (e.g., E4), enter:
=OFFSET($A$1:$T$1,0,COLUMN()-COLUMN($E$4)+(ROW()-ROW($E$4))*(COLUMNS($A$1:$T$1)/5),1,1)Step 2: Then drag down 5 rows and right 4 columns to form the matrix layout.
Formula explanation:
- $A$1:$T$1: The source row data.
- $E$4: The matrix's starting cell.
- COLUMNS($A$1:$T$1)/5: "5" is the number of matrix rows.
⚠️ Notes:
- Ensure the number of data points matches rows × columns exactly (e.g., 4×5 = 20).
- If the source range has blank cells, the matrix may display 0 in those spots.
- This formula method is dynamic but less intuitive for beginners. Ensure references are consistent.
Convert vector / single row or column to matrix with Kutools for Excel
If the formula approach feels too complex or time-consuming—especially for users unfamiliar with Excel functions or working with large data sets—Kutools for Excel offers a much simpler solution. Its Transform Range feature allows you to convert a single row or column into a matrix (or vice versa) through a visual interface, without writing a single formula. This method is ideal for users seeking efficiency and ease of use.
Step 1: Select the row or column range you want to transform into a matrix.
Step 2: Click Kutools > Range > Transform Range from the Excel ribbon.
Step 3: In the Transform Range dialog:
- Select Single row to range if you’re converting a horizontal row, or Single column to range for a vertical column.
- In the Fixed value box under Rows per record, enter the number of columns you want in the resulting matrix. For example, input 5 to create a 5-column matrix.
Step 4: Click OK. When prompted, select a blank cell where you want to output the matrix result.
Step 5: Click OK again. Your original row or column will now be rearranged into a matrix format instantly.
Using Kutools eliminates the need for complex formulas or manual fill operations, making it a perfect option for users who prioritize speed, simplicity, and accuracy. Note that this method requires the Kutools add-in, which may not be available in all environments.
Convert vector / single row or column to matrix using VBA Code
For users who need more advanced flexibility, want to automate the process, or handle large datasets, a VBA macro is an excellent choice. It enables custom matrix sizes, reduces repetitive manual steps, and works efficiently even with thousands of entries. This approach is ideal for repetitive reporting, batch data restructuring, or creating reusable Excel tools—without relying on external add-ins.
⚠ Important: Always save your workbook and enable macros before running this script. If your data size doesn't evenly fit the matrix dimensions, the extra cells will be left blank to avoid data loss.
Step 1: Press Alt + F11 to open the Microsoft Visual Basic for Applications editor. Click Insert → Module, and paste the code below:
Sub VectorToMatrix()
Dim vecRange As Range
Dim outCell As Range
Dim totalElements As Long
Dim matrixRows As Long, matrixCols As Long
Dim i As Long, j As Long, idx As Long
Dim xTitleId
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set vecRange = Application.InputBox("Select the vector (single row or column) to convert:", xTitleId, Type:=8)
If vecRange Is Nothing Then Exit Sub
matrixRows = Application.InputBox("Enter number of rows for the matrix:", xTitleId, , , , , , 1)
If matrixRows <= 0 Then Exit Sub
matrixCols = Application.InputBox("Enter number of columns for the matrix:", xTitleId, , , , , , 1)
If matrixCols <= 0 Then Exit Sub
totalElements = vecRange.Cells.Count
If matrixRows * matrixCols < totalElements Then
MsgBox "Matrix size does not fit all values from vector!", vbExclamation
Exit Sub
End If
Set outCell = Application.InputBox("Select the top-left cell for output matrix:", xTitleId, Type:=8)
If outCell Is Nothing Then Exit Sub
idx = 1
For i = 1 To matrixRows
For j = 1 To matrixCols
If idx <= totalElements Then
outCell.Cells(i, j).Value = vecRange.Cells(idx).Value
idx = idx + 1
Else
outCell.Cells(i, j).Value = ""
End If
Next j
Next i
End SubStep 2: To run the code, click inside the macro and press F5 or click the 
Run button. Then follow the prompts:
- Select the vector range — choose the single row or column to convert.
- Enter the number of rows and columns for the matrix.
- Select the top-left output cell for your matrix.
Once confirmed, the macro will automatically fill your target matrix with values from the selected vector. If the matrix size is too small, the script will abort and warn you before making any changes.
Why choose VBA? It’s ideal for batch processing, reusable logic, and data-heavy tasks that would be tedious with formulas or add-ins. If you often convert vectors into matrix layouts with different sizes or across many sheets, this method saves time and effort.
Troubleshooting: Ensure the selected input is a single row or column, and that the product of matrix rows × columns is greater than or equal to the number of source values. Always save before running, as this macro writes directly to your sheet.
Related article:
How to convert matrix to vector or single column in Excel?
Best Office Productivity Tools
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...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in