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.
Quickly Split one cell into columns or rows based on delimiter
|In Excel, to split a cell into columns is tedious with the Wizard step by step. But with Kutools for Excel's Split Cells utility, you can: 1,convert one cell into columns or rows based on delimiter; 2,convert string into text and number; 3,convert string based on specific width, with clicks. Click for full-featured 60 days free trial!|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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.
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.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
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.