Skip to main content

How to convert a single row to multiple columns and rows in Excel?

Author: Sun Last Modified: 2020-06-01

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?

doc single row to range 1

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 good idea3

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:

doc single row to range 2

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:

doc single row to range 3

3. Click Finish, then the single cell has been converted to multiple columns, and then resize the columns. See screenshots:

doc single row to range 4
doc arrow
doc single row to range 5

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:

doc single row to range 6
doc arrow
doc single row to range 7

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:

doc single row to range 9

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:

doc single row to range 9

3. Click Ok, then select a cell to put the result.

doc single row to range 10

4. Click OK. Now the single row has been converted to a range with multiple rows and columns.

doc single row to range 11

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.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Will 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."
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations