How to convert a single row to multiple columns and rows in Excel?

Suppose you have a row with a large data, and now you want to convert this single row to a range with multiple columns and rows as below screenshot shown, how can you solve it in Excel?

Here I introduce two methods may help you quickly solve this task.

Convert a cell row to multiple columns or rows with Text to Columns and Paste Transpose functions

Convert a single row to multiple columns and rows with Transform Range

Convert a cell row to multiple columns or rows with Text to Columns and Paste Transpose functions

In Excel, you only can convert a single cell to multiple columns or rows.

1. Select the cell you need to convert, and click Data > Text to columns. See screenshot:

2. In the popping dialog, check Delimited option firstly, click Next to go the step 2 of the dialog, and check Space option under Delimiters section. See screenshot:

3. Click Finish, then the single cell has been converted to multiple columns, and then resize the columns. See screenshots:

Note: If you want to convert the single cell values to multiple rows, you just need to convert it to multiple columns first, then select the column values and press Ctrl + C to copy them, then select a cell and right click to select Paste Special > Transpose. See screenshots:

But in Excel, you cannot quickly convert a single row data to a range with multiple columns and rows except manually copying and pasting, now I introduce a handy method for you.

Convert a single row to multiple columns and rows with Transform Range

If you install Kutools for Excel, you can apply the Transform Range utility to quickly convert a single column or a single row to a range.

After free installing Kutools for Excel, please do as below:

1. Select the single row you want to convert to a range, and click Kutools > Range > Transform Range. See screenshot:

2. In the Transform Range dialog, check Single row to range, then you can specify the Fixed value under Columns per record section. See screenshot:

3. Click Ok, then select a cell to put the result.

4. Click OK. Now the single row has been converted to a range with multiple rows and columns.

If you want to convert a range to a single column or single row, you also can apply Transform Range. Click here to get more about Transform Range.

Demo: Transpose Range