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

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:

 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.

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.

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.

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.

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:

This function supports Undo (Ctrl + Z).

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