Transpose / Convert columns and rows into single row with Kutools for Excel Note: In Set OutRng = OutRng.Offset(0, xCols + 0), you can change 0 to any number to meet your need.įor 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: Then Click Ok, the contents in the selected range are converted into a row. 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. Set OutRng = Application.InputBox('Paste to (single cell):', xTitleId, Type:=8)ģ. Set InputRng = Application.InputBox('Ranges to be transform :', xTitleId, InputRng.Address, Type:=8) Click Insert > Module, and paste the following code in the Module window. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.Ģ. The following VBA code also can help you convert a range of data to one row.ġ. Transpose / Convert columns and rows into single row with VBA code In this case, all the data in the range has been transposed into a single row in a new worksheet.

Then drag the fill handle over to right until number 0 is appeared. 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. Note: Sheet1!$A$1 is the worksheet and range references that you want to use. 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))) 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.ġ. Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari! Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Change column into row excel