Skip to main content

Transpose (convert) a single column or row to ranges or vise versa in Excel

Kutools for Excel

Boosts Excel With 300+
Powerful Features

The utility Transform Range in Kutools for Excel can help you transform (convert) a vertical column into several columns and convert a row into multiple rows quickly.

Transform or convert a column to multiple columns
Transform or convert a row to multiple rows
Transform or convert multiple columns to a single column
Transform or convert multiple rows to a single row

For example, if you have a column of data as shown as following screenshot, each customer’s information takes up 3 rows from up to down as name, Telephone and address. With Transform Range you can quickly convert that into three rows, like so:

And vice versa you can convert multiple columns or rows to a single column or row.


Click Kutools > Range > Transform Range. See screenshots:

shot transpose range 1 arrow-2

 Transform or convert a column to multiple columns

1. Select a single column that you want to convert into multiple columns, and then apply the utility by clicking Kutools > Range > Transform Range.

2. In the Transform Range dialog box, do the following settings.

1) The selected range is displayed in the Data to be transformed section. You can change the range if you need to;
2) In the Transform type section, select the Single column to range option;
3) In the Columns per record section, choose the option you need:
Blank cell delimited records: If there are blank cells in your column and you want to use them as separators to convert the column, you can choose this option. A new row will start at each blank cell.
Fixed value: If you want to transform every N rows from a column into multiple columns, you can choose this option, and then enter a number representing every N rows in the textbox.
4) Click OK.

3. In the next opening dialog box, select a single cell to output the result, and then click OK.
Tips: You can output the result in a different worksheet in the current workbook.

After completing the operation, you can see the result as follows.


 Transform or convert a row to multiple rows

1. Select a single row that you want to convert into multiple rows, and then apply the utility by clicking Kutools > Range > Transform Range.

2. In the Transform Range dialog box, do the following settings.

1) The selected range is displayed in the Data to be transformed section. You can change the range if you need to;
2) In the Transform type section, select the Single row to range option;
3) In the Rows per record section, choose the option you need:
Blank cell delimited records: If there are blank cells in your row and you want to use them as separators to convert the row, you can choose this option. A new column will start at each blank cell.
Fixed value: If you want to transpose every N columns from a row into multiple rows, you can choose this option, and then enter a number in the textbox.
4) Click OK.

3. In the next opening dialog box, select a single cell to output the result, and then click OK.
Tips: You can output the result in a different worksheet in the current workbook.

After completing the operation, you can see the result as follows.


 Transform or convert multiple columns to a single column

Sometimes you need to convert a range to a single column, with this Transform Range tool, you can deal with it easily and quickly. Do as follows:

1. Select the range that you want to convert to a single column, and then enable this feature by clicking Kutools > Range > Transform Range.

1) The selected range is displayed in the Data to be transformed section. You can change the range if you need to;
2) In the Transform type section, select the Range to single column option;
3) Click OK.

3. In the next opening dialog box, select a single cell to output the result, and then click OK.
Tips: You can output the result in a different worksheet in the current workbook.

Then you can see the result as follows.


 Transform or convert multiple rows to a single row

To transform multiple rows to a single row, you can do as this:

1. Select the range that you want to convert to a single row, and then enable this feature by clicking Kutools > Range > Transform Range.

1) The selected range is displayed in the Data to be transformed section. You can change the range if you need to;
2) In the Transform type section, select the Range to single row option;
3) Click OK.

3. In the next opening dialog box, select a single cell to output the result, and then click OK.
Tips: You can output the result in a different worksheet in the current workbook.

Then you can see the result as follows. 


 Notes:

arrow This function supports Undo (Ctrl + Z).

arrow If there is a large data and you want to speed up the transformation, check the Don't keep formatting (processing faster) option in the lower left cornor of the dialog box. It is important to mention that if you check this option, the formatting of the cells will not be preserved in the output range.


Demo: Transform a range of cells to a single row or column and vise versa

Kutools for Excel: with more than 300 handy features, free to try with no limitation in 30 days. ownload and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Question on "range to single row": I have a survey data. I need different household members data, in multiple row and column to be in one row and multiple column for a household. I want almost the same output with the above example but want it to look like A1, A2, A3, B1,B2,B3,C1,C2,C3 in multiple column with different length but all in one row. Could you help?
This comment was minimized by the moderator on the site
i want convert table data(which consist 4 raw 4 column) into each single raw in very quickly. each table are differentiate with one single raw
This comment was minimized by the moderator on the site
I have a simple file I'd like to send you to see if your macro can be used to handle it. I know it can be used to remove blank rows & columns, which is helpful. But I need something a little different than what I reviewed in your demo videos. How can I send you a simple Excel file?
This comment was minimized by the moderator on the site
[quote]I have a simple file I'd like to send you to see if your macro can be used to handle it. I know it can be used to remove blank rows & columns, which is helpful. But I need something a little different than what I reviewed in your demo videos. How can I send you a simple Excel file?By Steve Newbern[/quote] Hello, Please contact me at jaychivo#extendoffice.com. Please replace @ with #.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations