How to convert multiple rows and columns into a single row in Excel?
Combining a range containing multiple rows and columns into one long row manually can be time-consuming, especially with a large dataset. This article explains three ways to convert the range quickly: an Excel formula, VBA, and Kutools for Excel.
| Method | Best for | Key consideration |
|---|---|---|
| Formula | Creating a result that remains linked to the source values | Requires entering and filling a formula across the row |
| VBA | Automating a one-time conversion | Requires a macro-enabled environment |
| Kutools for Excel | Converting a selected range without formulas or code | Creates the single-row result in a selected location |
Convert multiple rows and columns into a single row with a formula
Suppose your source data is in Sheet1!A1:D5, as shown below. The following formula returns the values row by row in a single horizontal row.
- In a new worksheet in the same workbook, select cell A1 and enter this formula:
=IFERROR(INDEX(Sheet1!$A$1:$D$5,1+INT((COLUMN(A1)-1)/COLUMNS(Sheet1!$A$1:$D$5)),MOD(COLUMN(A1)-1,COLUMNS(Sheet1!$A$1:$D$5))+1),"")
- Drag the fill handle to the right until all values from the source range appear in one row.
📝 Note:
Sheet1!$A$1:$D$5 is the source range. Replace it with the worksheet name and range containing your data. The formula reads the source range from left to right, one row at a time.
Convert multiple rows and columns into a single row with VBA
The following VBA code copies each row in the selected range and places the rows side by side.
- Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the following code into the Module window.
Sub TransformOneRow() Dim InputRng As Range Dim OutRng As Range Dim xTitleId As String Dim xRows As Long Dim xCols As Long Dim i As Long xTitleId = "KutoolsforExcel" On Error Resume Next Set InputRng = Application.InputBox("Select the range to transform:", xTitleId, Selection.Address, Type:=8) If InputRng Is Nothing Then Exit Sub Set OutRng = Application.InputBox("Select the first destination cell:", xTitleId, Type:=8) If OutRng Is Nothing Then Exit Sub On Error GoTo 0 Application.ScreenUpdating = False xRows = InputRng.Rows.Count xCols = InputRng.Columns.Count For i = 1 To xRows InputRng.Rows(i).Copy OutRng Set OutRng = OutRng.Offset(0, xCols) Next i Application.CutCopyMode = False Application.ScreenUpdating = True End Sub - Press F5 to run the code. Select the source range in the first dialog box, click OK, and then select the first destination cell in the second dialog box.
>>> 
- Click OK. The contents of the selected range are converted into a single row.
>>> 
>>> 
>>> 
📝 Note:
To leave one blank column between the values taken from each original row, change Set OutRng = OutRng.Offset(0, xCols) to Set OutRng = OutRng.Offset(0, xCols + 1).
Convert multiple rows and columns into a single row with Kutools for Excel
The formula requires adjusting a range reference, while VBA requires running code. Kutools for Excel's Transform Range feature converts the selected range into a single row without formulas or macros.
- Select the range you want to convert.
- Click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Range to single row.
- Click OK, and select the first cell where you want to place the result.
- Click OK. The selected range is converted into a single row.
>>> 
Learn more about the Transform Range feature.
Download Kutools for Excel – Free 30-day trial
Frequently asked questions
In what order are the cells placed in the single row?
All three methods process the source range row by row, from left to right. After the values in the first source row, they continue with the first value in the next row.
Will the result update when the source data changes?
The formula result updates when the source values change. The VBA and Kutools methods create static results that do not update automatically.
Why is Kutools not showing in my Excel?
Kutools may not be installed, enabled, or loaded correctly. Make sure it is installed, then go to File > Options > Add-ins to check whether Kutools is disabled. If it is installed and enabled but still does not appear, restart Excel or reinstall Kutools.
Related articles
How to change row to column in Excel?
How to transpose / convert a single column to multiple columns in Excel?
How to transpose / convert columns and rows into single column?
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