How to transpose columns and rows when pasting data in Excel
Transposing data allows you to convert a vertical column into a horizontal row or a horizontal row into a vertical column. This article introduces two ways to transpose columns and rows when pasting data in Excel.
- Transpose columns and rows with Paste Special
- Transpose columns and rows with Kutools for Excel
- Frequently asked questions
| Method | Best for | Key advantage |
|---|---|---|
| Paste Special | Transposing a copied row or column | Built into Excel and suitable for a quick, one-time transpose |
| Kutools for Excel | Transforming rows, columns, or ranges into different layouts | Provides several transformation options in one dialog box |
Suppose you have a column of data, as shown below, and want to transpose it into a row when pasting it.
Transpose columns and rows with Paste Special
Excel’s Paste Special feature can transpose a copied column into a row or a copied row into a column.
- Select the range you want to transpose, and then press Ctrl + C to copy it.
- Right-click the blank cell where you want to place the transposed data, and select Paste Special > Paste Special.
- In the Paste Special dialog box, select the Transpose checkbox, and click OK.
The selected column is transposed into a row.
📝 Note:
The transposed result is a separate copy and does not remain linked to the original data.
Transpose columns and rows with Kutools for Excel
For more transformation options, the Transform Range feature of Kutools for Excel can transform a column into a row, a row into a column, or a single row or column into a multi-row and multi-column range.
Kutools for Excel includes more than 300 useful Excel tools. You can try all features free for 30 days. Download Kutools for Excel and start your free trial.
- Select the column you want to transpose, and click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Range to single row, and click OK.
- In the next Transform Range dialog box, select a blank cell where you want to place the result, and click OK.
The selected column is immediately transposed into a row.
The Transform Range feature can also transform a row into a column, a single column into a range, or a single row into a range. Learn more about the Transform Range feature.
Download Kutools for Excel and start your 30-day free trial now!
Frequently asked questions
Can I transpose a row into a column using Paste Special?
Yes. Copy the row, open the Paste Special dialog box, and select Transpose. The row will be pasted vertically as a column.
Does the transposed data update when the original data changes?
No. Paste Special and Transform Range create separate results. Changes made to the original data are not automatically reflected in the transposed range.
Can I transpose formulas as well as values?
Yes. Paste Special can transpose formulas, but relative references may change based on the new positions. Check the formulas after transposing to ensure that they reference the intended cells.
Why is the Transpose option unavailable in Excel?
Make sure you copied the source range instead of cutting it. The option may also be unavailable if the source or destination contains merged cells.
Why is Kutools not showing in my Excel ribbon?
Kutools for Excel must be installed and enabled before its tabs appear. If it is already installed, check whether it has been disabled under File > Options > Add-ins.
Related articles
- How to paste all except formulas in Excel?
- How to copy a column and paste only the unique records in Excel?
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