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.
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.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
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.
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 years agoWill this program work on Excel for Mac 2011? If so, how do you download it? When I tried I get a box that says "Safari can’t open the file “KutoolsforExcelSetup.exe” because no available application can open it."