Skip to main content

How to transpose / convert columns and rows into single row?

How to join multiple rows and columns into a single long row? Maybe, it seems easy for you, because you can copy them one by one and join them into a row manually. But it will be time consuming and tedious if there are hundreds of rows and columns. Here I will talk about some quick tricks to solve it.

Transpose / Convert columns and rows into single row with formula

Transpose / Convert columns and rows into single row with VBA code

Transpose / Convert columns and rows into single row with Kutools for Excel


arrow blue right bubble Transpose / Convert columns and rows into single row with formula

Supposing you have a range of data as following screenshot shows, you can convert the range data into one row in a new worksheet with a long formula.

doc-convert-range-to-row1

Please apply the formula as this:

1. In a new worksheet of the active workbook, click cell A1, copy and paste this formula: =OFFSET(Sheet1!$A$1,((ROW()-1)*5)+(FLOOR(COLUMN()-1,4)/4),(COLUMN()-1)-(FLOOR(COLUMN()-1,4)))

Note: Sheet1!$A$1 is the worksheet and range references that you want to use.

ROW()-1)*5 in the above formula, 5 stands for the row number; and COLUMN()-1,4)/4, the 4 stands for the column number. You can change them as your need.

2. Then drag the fill handle over to right until number 0 is appeared. In this case, all the data in the range has been transposed into a single row in a new worksheet. See screenshot:

doc-convert-range-to-row2


arrow blue right bubble Transpose / Convert columns and rows into single row with VBA code

The following VBA code also can help you convert a range of data to one row.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

Sub TransformOneRow()
'Updateby20131120
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Ranges to be transform :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
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 + 0)
Next
Application.ScreenUpdating = True
End Sub

3. Then press F5 key to run the code, a dialog is popped out for you to select a range of contents you want to convert into a row, and click Ok, another popped-up dialog is for you to select a single cell to put out the result. See screenshots:

Then Click Ok, the contents in the selected range are converted into a row. See screenshot:

Note: In Set OutRng = OutRng.Offset(0, xCols + 0), you can change 0 to any number to meet your need.

For exampel, if you want to separate the results accroding to the original rows by one column, you can change Set OutRng = OutRng.Offset(0, xCols + 0) to Set OutRng = OutRng.Offset(0, xCols + 1), the results are shown as following:


arrow blue right bubble Transpose / Convert columns and rows into single row with Kutools for Excel

It seems the above two methods are somewhat difficult for us Excel beginner, here I will talk about a handy tool-Kutools for Excel for you.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now

With the utility Transform Range of Kutools for Excel, you can quickly transform a range to a single row, please do as follows:

1. Select the range that you want to transpose.

2. Click Kutools > Range Converter > Transform Range, see screenshot:

doc-convert-range-to-row4

3. In the Transform Range dialog box, select Range to single row option, see screenshot:

doc-convert-range-to-row5

4. Then click OK, and click a cell to put the result from the pop out box.

doc-convert-range-to-row6

5. Click OK, and the data in the range has been transposed into a single row. See screenshots:

doc-convert-range-to-row1
-1
doc-convert-range-to-row7

If you want to know more about this feature, please visit Transform Range.


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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have the following data set.
2 0.350563636 0.846454545 1.927818182 2.288090909 19.13436364 39.82727273 3.307545455
3 38.12 38.53636364 32.42454545 1790.618182 70.78181818 99.81181818 71.4 7.95E-05 0.000110545
4 0.765436364 0.905981818 5.759181818 43.40363636 2.136818182
in excel.
I want to convert in to single row side by side like as follow:


2 0.350563636 0.846454545 1.927818182 2.288090909 19.13436364 39.82727273 3.307545455 3 38.12 38.53636364 32.42454545 1790.618182 70.78181818 99.81181818 71.4 7.95E-05 0.000110545 4 0.765436364 0.905981818 5.759181818 43.40363636 2.136818182


How is it possible?
Thanks
This comment was minimized by the moderator on the site
From:

A B C
E F G
H I J



To:

A B C E F G H I J

How to change from above to single row?
This comment was minimized by the moderator on the site
Have you been able to accomplish this? I'm having the same problem.
This comment was minimized by the moderator on the site
Hello, i would to know how to convert 10 rows in excel into 10 different pdf's (or) word document in java coding. Thank you
This comment was minimized by the moderator on the site
Transform range in Kutools did not work. Waste of time. Install de-installed my ASAP utilities WITHOUT permission. Total B.S.
This comment was minimized by the moderator on the site
i want to convert following word table pf 250 basic 3000 da 2000 wa 100 hra 1500 oa 300 into pf wa basic hra da oa 250 100 3000 1500 2000 300 can anyone help me
This comment was minimized by the moderator on the site
I need to convert the following type data to a single row as mentioned last; ----------------------------------------------------------------------------------------------------------------------------------------------------------- A B C D E F G H ----------------------------------------------------------------------------------------------------------------------------------------------------------- Sl.No. - Mem.No. - Name & Address - Parents Name - Qualification - Occupation - Present Address - Remarks ----------------------------------------------------------------------------------------------------------------------------------------------------------- 1 103 Rajesh Govind BA Welder Rajesh G X KayGee Villa Dubai AN Apartments Kastur Marg RN Street Coimbatore Bangaluru Tamil Nadu Karnataka 2 104 Ganesh 1 - 103 - Rajesh - KayGee Villa - Kastur Marg - Coimbatore - Tamil Nadu - Govind - - - - BA - - - - Welder - Dubai - - - - Rajesh G - AN Apartments - RN Street - Bangaluru - Karnataka - X 2 - 104 - Ganesh - ................................................
This comment was minimized by the moderator on the site
I tried the above formula: =OFFSET(Sheet1!$A$1,((ROW()-1)*5)+(FLOOR(COLUMN()-1,4)/4),(COLUMN()-1)-(FLOOR(COLUMN()-1,4))) but all it does is place the value from sheet1 A1 in all of the columns. Why won't it display the other data? Example: 1 2 3 4 5 6 7 8 9 1 2 3 Becomes this after adding your formula: 1 1 1 1 1 1 1 1 1 1 1 1
This comment was minimized by the moderator on the site
ABC TRAVELS KING'S ST. KANDY 081-9999999 DEF TRAVELS KING'S ST. KANDY 081-8888888 -- -- about 500 travels like in same row as above, and there is blank row in between each travel data. I would like to have this as following in easy way. column A Column B Column C ABC TRAVELS KING'S ST. KANDY 081-9999999 DEF TRAVELS KING'S ST. KANDY 081-8888888 --- Please help
This comment was minimized by the moderator on the site
I have a fix range in sheet1 like (D8:F14). How to transpose it to sheet2 fixed row like (C3:W3)? Thanks for your response.
This comment was minimized by the moderator on the site
Could you please help me to transpose / convert single row (wrap text) into rows? Thanks in advance!!!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations