How to convert a single row to multiple columns and rows in Excel?
In Excel, users often encounter situations where they need to reorganize a long, single row of data into a more readable format with multiple columns and rows. This transformation is common when dealing with imported, exported, or manually compiled data that arrives in a horizontal layout, but needs to be analyzed, processed, or presented in a tabular grid. For example, you may have a dataset listed across a single row, and wish to reformat it into a grid structure, as illustrated below:
Converting a row of data into a structured table format has several advantages: it improves readability, facilitates easier data analysis, and enables the use of a wider range of Excel’s tools, such as filtering and charting. Depending on your specific requirements, Excel provides multiple ways to achieve this transformation—ranging from built-in features and formulas, to dedicated add-ins and automation through VBA code. In this article, we’ll cover several practical approaches, each suitable for different scenarios and user preferences.
Contents:
- Convert a cell row to multiple columns or rows with Text to Columns and Paste Transpose functions
- Convert a single row to multiple columns and rows with Transform Range
- Convert a single row to multiple columns and rows with an Excel INDEX formula
- Convert a single row to multiple columns and rows with VBA Code
Convert a cell row to multiple columns or rows with Text to Columns and Paste Transpose functions
Excel provides the Text to Columns and Paste Special (Transpose) features for basic data reorganization. These options are useful if your data is confined to a single cell or needs a simple restructuring.
1. Select the cell you need to convert, and click Data > Text to Columns. See screenshot:
2. In the dialog that appears, first choose the Delimited option, then click Next. In the next step, select Space (or the actual delimiter your data uses) under the Delimiters section. See screenshot:
3. Click Finish. Your single cell will be split into multiple columns based on the specified delimiter. You may want to adjust the column widths for better visibility. See screenshots:
 |
 |
 |
Note: If you want to further convert those column values into multiple rows:
- Select the values in the new columns, press Ctrl + C to copy.
- Right-click on the cell where you want to start the transposed data and choose Paste Special > Transpose. See screenshots:
 |
|
 |
This approach is straightforward for simple transformations and when the amount is manageable. However, it is best suited to cases where the initial data is in a single cell. If you have a single row (not just a single cell) of data to rearrange into a grid of multiple columns and rows, these features are not sufficient on their own, and further manual adjustment would be required. For more advanced needs, continue with the next solutions.
Convert a single row to multiple columns and rows with Transform Range
If you have Kutools for Excel installed, you can take advantage of the Transform Range tool. This feature enables you to convert single-row or single-column data layouts directly into multi-row and multi-column ranges, all in just a few clicks. It's especially helpful for handling large datasets and minimizes the need for manual copying or complex formulas.
After free installing Kutools for Excel, follow these steps:
1. Select the single row you want to convert, then navigate to Kutools > Range > Transform Range. See screenshot:
2. In the Transform Range dialog, select Single row to range. In the Columns per record area, specify how many columns you want each row of the result to contain. You can set this number based on your preferred layout or the requirements of your report. See screenshot:
Tips:
- The "Fixed value" for columns determines how many items each new row will hold. For example, if you have a single row of 18 values and set columns per record to 6, your data will be reshaped into 3 rows of 6 columns each.
- If your row length doesn’t divide evenly, the last row may have fewer columns than specified.
3. Click OK. A dialog prompts you to select a destination cell for the transformed result. Pick a cell outside your original range to avoid overwriting data.
4. Click OK again. Your single row will now be converted into a neatly arranged range with multiple rows and columns. See the result below:
Notes:
- This feature also works in reverse—allowing you to compress multiple-column/row ranges into a single row or column if needed. Click here to get more about Transform Range.
- Be sure to back up your data or operate on a copy, especially if your source or destination ranges may overlap.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Convert a single row to multiple columns and rows with an Excel INDEX formula
If you want a formula-based approach—ideal for users who prefer not to use add-ins or wish to automate with worksheet formulas—Excel’s INDEX function, often combined with SEQUENCE (available in Microsoft 365 and newer), allows you to reshape a single row into a two-dimensional range efficiently.
This method helps maintain a dynamic link between your original data and the output: any changes in your original row are automatically reflected in the multi-row, multi-column output.
Scenario Example: Suppose your data is in row 1, from cell A1 to R1 (total of 18 items), and you want to arrange them into 6 columns and 3 rows.
1. Select the upper left cell of your desired output range (e.g., cell A3).
2. Enter the following formula in cell A3:
=INDEX($A$1:$R$1, SEQUENCE(3,6))Parameter explanation: The range $A$1:$R$1 is your original row. 6 is the number of columns for each row of the output. Adjust these as needed.
It will automatically fill a 3x6 grid (works in dynamic array-enabled Excel only).
Limitations: For very large datasets, complex rearrangement needs, or when a custom order is required, a formula approach may become bulky or difficult to manage. In such cases, consider automation with VBA (see below).
Convert a single row to multiple columns and rows with VBA Code
For full automation and more control, you can use a VBA macro to convert a single row into a custom-sized grid of columns and rows. This approach is efficient for repetitive tasks and large datasets, and can be easily customized for different output sizes.
Typical use case: You have a single row with a variable length, wish to split it into a defined number of columns per row (e.g., every 6 values become a new row), and want to avoid manual or formula-based transformations.
Instructions:
1. Open Excel and press Alt + F11 to launch the VBA editor.
2. In the VBA editor, click Insert > Module, and paste the following code into the module window:
Sub RowToMultiRowCol()
Dim inputRng As Range
Dim outputCell As Range
Dim nCols As Integer
Dim nData As Integer
Dim i As Integer
Dim r As Integer
Dim c As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set inputRng = Application.InputBox("Select the single row to convert", xTitleId, "", Type:=8)
Set outputCell = Application.InputBox("Select the top-left cell for the result", xTitleId, "", Type:=8)
nCols = Application.InputBox("Number of columns per row:", xTitleId, "6", Type:=1)
On Error GoTo 0
If inputRng Is Nothing Or outputCell Is Nothing Or nCols <= 0 Then Exit Sub
nData = inputRng.Columns.Count
For i = 1 To nData
r = Int((i - 1) / nCols)
c = ((i - 1) Mod nCols)
outputCell.Offset(r, c).Value = inputRng.Cells(1, i).Value
Next i
End Sub3. Close the VBA editor. In Excel, go to Developer Tools > Macros, select RowToMultiRowCol, and click Run.
4. The macro will prompt you to:
- Select the single row range to be transformed.
- Choose the top-left cell for where you want the output to appear (ensure this range does not overlap with your input).
- Enter the desired number of columns per row (e.g.,6 for arranging into multiple rows of 6 columns each).
The macro will quickly and efficiently populate your output area with the rearranged data.
Advantages: Provides fast one-click operation and flexibility in defining the output shape. Suitable for complex scenarios, large datasets, and when frequent use is required.
Tips: Always save your workbook before running macros, as VBA actions cannot be undone.
Demo: Transpose Range
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