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
| 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.
Transpose Range
Best Office Productivity Tools
Easy to Uninstall Completely | Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Spreadsheets: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
