Transpose (convert) a single column or row to ranges or vise verse 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 Demo

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: Demo

And vise verse you can convert multiple columns or rows to a single column or row.

Click Kutools > Range Converter > Transform Range. See screenshots:

Transform or convert a column to multiple columns

1. Please select a single column which you want to convert into multiple columns as follows (see screenshot), and then apply the utility.

2. Specify the setting in Transform Range dialog box as follows (see screenshot). Note: Blank cell delimits records option will start a new row at each blank cell while converting the column into multiple columns. If you have selected a column with blank cells as separator, and you want to use the blank cell as separator to convert the column, please check this option. Fixed Value option will start a new row after how many cells in per row.

3. Click OK. And specify the Output range to contain the result. Note: The Output range can be specified in different worksheets and workbooks. See screenshot:

4. Click OK. You will see the result as follows. See screenshots:

Transform or convert a row to multiple rows

1. Please select a single row which you want to convert into multiple rows as follows (see screenshot), and then apply the utility.

2. Specify the setting in Transform Range dialog box as follows (see screenshot). Note: Blank cell delimits records option will start a new column at each blank cell while converting the row into multiple rows. If you have selected a row with blank cells as separator, and you want to use the blank cell as separator to convert the row, please check this option. Fixed Value option will start a new column after how many cells in per column.

3. Click OK. And specify the Output range to contain the result. Note: The Output range can be specify in different worksheets and workbooks. See screenshot:

4. Click OK. You will see the result as follows. See screenshots:

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

2. Specify the setting in Transform Range dialog box as follows:

3. Click OK. And specify the Output range to contain the result. Note: The Output range can be specify in different worksheets and workbooks. See screenshot:

4. Click OK. You will see the result as follows. See screenshots:

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

2. Specify the setting in Transform Range dialog box as follows:

3. Click OK. And specify the Output range to contain the result. Note: The Output range can be specify in different worksheets and workbooks. See screenshot:

4. Click OK. You will see the result as follows. See screenshots:

Notes:

This function supports Undo (Ctrl + Z).

The data to be transformed keeps intact after transforming the vertical range.

The Data to be transformed range and the Output range can be in different worksheets or workbooks.

See Transform Vertical Range demo (video 1)

This is only one feature of Kutools for Excel

It includes more than 120 functions and tools for Excel 2013, 2010, 2007 and Office 365.