Contents:
How to convert a single row into multiple rows and columns in Excel?
A long row of data can be difficult to read, analyze, or present. You may need to split text stored in one cell, transpose values into a column, or reshape an existing row into a grid with a fixed number of columns. This tutorial introduces four methods for these different situations.
| Method | Best for | Result | Main consideration |
|---|---|---|---|
| INDEX formula | Microsoft 365 and dynamic-array Excel users who need linked results | Returns a dynamic grid that updates with the source row | Requires a dynamic array-enabled version of Excel |
| Kutools Transform Range | Quickly reshaping an existing row without formulas or code | Creates a grid using a fixed number of columns per record | Requires Kutools for Excel |
| VBA | Repeatable tasks and custom output sizes | Creates a static grid using the number of columns you specify | Requires macros to be enabled |
| Text to Columns and Paste Transpose | Delimited text stored in a single cell | Splits the text into columns and can then transpose it into one column | Not designed to reshape an existing multi-cell row directly into a grid |
Convert a single row into multiple rows and columns with an INDEX formula
In Microsoft 365 and other dynamic array-enabled versions of Excel, you can combine INDEX with SEQUENCE to create a grid that remains linked to the source row.
Suppose the source data is in A1:R1 and contains 18 values. To arrange the values into 3 rows and 6 columns:
- Select the upper-left cell of the output range, such as A3.
- Enter the following formula and press Enter:
=INDEX($A$1:$R$1,1,SEQUENCE(3,6))
The formula spills automatically into a 3-by-6 grid. In the formula, $A$1:$R$1 is the source row, 3 is the number of output rows, and 6 is the number of output columns.
📝 Note:
This formula requires a version of Excel that supports dynamic arrays. The output also needs enough empty cells to spill into the complete grid.
Convert a single row into multiple rows and columns with Transform Range
Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Integrated with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel... Free trial...
With Kutools for Excel, the Transform Range feature can reshape an existing row into a multi-row, multi-column range without formulas or VBA.
After installing Kutools for Excel, follow these steps:
- Select the single row that you want to convert, and click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Single row to range. Under Columns per record, select Fixed value and enter the number of columns that each output row should contain.
- Click OK, and then select a destination cell outside the source range.
- Click OK. The single row is converted into a range containing multiple rows and columns.
📝 Notes:
- For example, if the source row contains 18 values and you specify 6 columns per record, the result will contain 3 rows and 6 columns.
- If the number of source values is not evenly divisible by the fixed value, the final output row will contain fewer values.
- The feature can also transform a range into a single row or column. Learn more about Transform Range.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Convert a single row into multiple rows and columns with VBA
VBA is useful when you frequently need to reshape rows of different lengths and want to specify the number of columns each time the macro runs.
- Press Alt + F11 to open the Microsoft Visual Basic for Applications editor.
- Click Insert > Module, and paste the following code into the module:
Sub RowToMultiRowCol() Dim inputRng As Range Dim outputCell As Range Dim nCols As Long Dim nData As Long Dim i As Long Dim r As Long Dim c As Long Dim xTitleId As String On Error Resume Next xTitleId = "Kutools for Excel" 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 Sub - Close the VBA editor. In Excel, click Developer > Macros, select RowToMultiRowCol, and click Run.
- Follow the prompts to select the source row, select the upper-left destination cell, and enter the number of columns for each output row.
📝 Notes:
- Select a destination that does not overlap the source row.
- Save the workbook before running the macro because VBA actions cannot be undone.
- Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the code.
If the data is stored in one cell: Use Text to Columns and Paste Transpose
Excel's Text to Columns and Paste Special (Transpose) features work well when several values are stored in one cell and separated by spaces, commas, or another delimiter.
- Select the cell that contains the delimited text, and click Data > Text to Columns.
- In the wizard, select Delimited, and click Next. Then select Space, or choose the delimiter used in your data.
- Click Finish. The contents of the cell are split into separate columns.
To transpose the new column values into rows:
- Select the split values and press Ctrl + C to copy them.
- Right-click the destination cell and select Paste Special > Transpose.
📝 Note:
This method is intended for delimited text contained in one cell. It does not directly reshape an existing multi-cell row into a grid of multiple rows and columns.
Frequently Asked Questions
Which method should I use if the values are stored in one cell?
Use Text to Columns when one cell contains several values separated by spaces, commas, or another delimiter. The other methods are intended for values already stored in separate cells across a row.
How do I change the number of columns in each output row?
With Kutools, change the Fixed value under Columns per record. In the INDEX formula, change the second argument of SEQUENCE. The VBA macro asks for the number whenever it runs.
Why does the INDEX formula return a #SPILL! error?
The output area contains data or merged cells that block the dynamic array result. Clear the entire area required by the formula and try again.
Will the transformed result update when the source row changes?
The INDEX formula updates automatically. Text to Columns, Kutools, and VBA create separate results that must be generated again after the source data changes.
What happens if the number of source values does not fill the final row?
The final row will contain fewer values. With a fixed-size INDEX formula, cells beyond the source range may return an error, so set the output dimensions to match the amount of source data.
Demo: Transform a single row into a range
Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy AI-powered features for smarter and faster work! Download Now!
Best Office Productivity Tools
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!
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