How to change rows to columns or columns to rows in Excel?
Sometimes, you may need to switch rows to columns or columns to rows in Excel. This article explains how to transpose a single row or column, transpose a larger range, and reorganize one row or column into multiple rows or columns.
| Method | Best for | Setup | Flexibility |
|---|---|---|---|
| Paste Transpose | Switching a complete row, column, or range between horizontal and vertical layouts | Copy the range and select Transpose | Transposes the copied range as one unit |
| Kutools for Excel – Transform Range | Converting a row or column into one or multiple rows or columns | Select the source range and the required transform type | Supports fixed record sizes and records separated by blank cells |
Change a row to a column with Paste Transpose
Suppose you have data arranged in one row, as shown below, and want to convert it into a column.
- Select the row and press Ctrl + C to copy it.
- Select the first destination cell.
- Right-click the cell and select the Transpose Paste option.
The copied row is converted into a column:
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Change multiple rows to multiple columns with Paste Transpose
You can use the same Transpose Paste option to switch multiple rows to multiple columns.
- Select and copy the rows.
- Select the first destination cell.
- Right-click the cell and select the Transpose Paste option.
Change a column to a row with Paste Transpose
You can also use Transpose to convert a single column into a row.
- Select and copy the column.
- Select the first destination cell.
- Right-click the cell and select the Transpose Paste option.
Change multiple columns to multiple rows with Paste Transpose
To switch multiple columns to multiple rows, copy the complete range and use the Transpose Paste option.
- Select and copy the columns.
- Select the first destination cell.
- Right-click the cell and select the Transpose Paste option.
Convert a row or range with Kutools for Excel
With the Transform Range feature in Kutools for Excel, you can convert a single row into multiple columns or combine an entire range into a single column.
Convert a single row into multiple columns
- Select the row, and click Kutools > Range > Transform Range.
- Select Single row to range. Select Fixed value and enter the number of items for each column.
- Click OK, select the first output cell, and click OK again.
Convert a range into a single column
- Select the range, and click Kutools > Range > Transform Range.
- Select Range to single column, and click OK.
- Select the first output cell, and click OK.
Convert one column into multiple rows and columns with Kutools for Excel
The built-in Transpose command switches an entire range, but it does not split one column into a specified number of records. Transform Range can divide a column using a fixed record size or blank cells as separators.
Split a column by a fixed number of values
- Select the column, and click Kutools > Range > Transform Range.
- Select Single column to range. Select Fixed value and enter the number of items for each row.
- Click OK, select the first output cell, and click OK again.
Split a column at blank cells
To create a new record whenever a blank cell appears, select Blank cell delimits records instead of Fixed value.
Frequently Asked Questions
Does Paste Transpose move or copy the original data?
It creates a transposed copy and leaves the original data unchanged. Delete the original range after checking the result if you want to move the data.
What is the difference between Paste Transpose and Transform Range?
Paste Transpose switches the rows and columns of the entire copied range. Transform Range can also split a row or column into multiple records and combine a range into one row or column.
Can I control how many items appear in each output row or column?
Yes. Select Fixed value in the Transform Range dialog box and enter the required number.
Can blank cells be used to separate records?
Yes. Select Blank cell delimits records to begin a new output row at each blank cell.
Will the converted results update when the source data changes?
No. These methods create static results. Run the operation again after changing the source data.
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in