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 30 days free trial!|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.