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
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
