How to transpose a column to a row or vice versa in Excel?
If your data is arranged vertically in a column but needs to appear horizontally in a row—or the other way around—Excel provides several ways to transpose it. This article explains how to use the TRANSPOSE function, Paste Special, and Kutools for Excel.
| Method | Best for | Result |
|---|---|---|
| TRANSPOSE function | Keeping the transposed result linked to the source data | Updates when the source values change |
| Paste Special | Quickly creating a one-time transposed copy | Static values that do not update with the source |
| Kutools for Excel | Transposing a selection without formulas and performing additional range transformations | Static output placed in a selected location |
Transpose a column to a row or vice versa with the TRANSPOSE function
The TRANSPOSE function changes a vertical range into a horizontal range or a horizontal range into a vertical one. The result remains linked to the source data.
- In Excel for Microsoft 365, Excel 2024, or Excel 2021, select the first blank cell where you want the result to appear. In Excel 2019 or an earlier version, first select a blank output range with the opposite orientation but the same number of cells as the source range.
- Enter the following formula, replacing $A$1:$A$11 with your source range:
=TRANSPOSE($A$1:$A$11)
- Press Enter in Excel for Microsoft 365, Excel 2024, or Excel 2021. The results spill automatically into the adjacent cells. In Excel 2019 or an earlier version, press Ctrl + Shift + Enter to enter the formula as an array formula.
📝 Note:
To transpose a horizontal row into a vertical column, use the same function with the horizontal source range as its argument.
Transpose a column to a row or vice versa with Paste Special
Paste Special is a convenient choice when you need a one-time transposed copy that is not linked to the source cells.
- Select the column or row you want to transpose, and press Ctrl + C to copy it.
- Right-click the first destination cell, and choose Paste Special from the context menu.
- In the Paste Special dialog box, select All under Paste, select None under Operation, and check Transpose.
- Click OK. The copied column is pasted as a row, or the copied row is pasted as a column.
Transpose a column to a row or vice versa with Kutools for Excel
Kutools for Excel's Transform Range feature can convert a column into a row or a row into a column without a formula. It can also transform a multi-row and multi-column range into a single row or column.
- Select the cells you want to transpose, and then click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Range to single row to create a horizontal row, or Range to single column to create a vertical column.
- Click OK, and select the first cell where you want to place the result.
- Click OK. The selected data is placed in the chosen orientation.
Learn more about the Transform Range feature.
Download Kutools for Excel – Free 30-day trial
Frequently asked questions
Why does the TRANSPOSE formula return a #SPILL! error?
One or more cells in the intended spill range are not empty. Clear those cells or move the formula to an area with enough blank space.
Why is Transpose unavailable in Paste Special?
Make sure you copied the source cells with Ctrl + C. The option may be unavailable if you cut the cells, are editing a cell, or are copying between incompatible ranges.
Will the transposed result update when the source data changes?
The TRANSPOSE formula updates with the source values. Results created with Paste Special or Transform Range are static and do not update automatically.
Why is Kutools not showing in my Excel?
Kutools may not be installed, enabled, or loaded correctly. Make sure it is installed, then go to File > Options > Add-ins to check whether Kutools is disabled. If it is installed and enabled but still does not appear, restart Excel or reinstall Kutools.
Demo: Transpose a column to a row or vice versa with Kutools for Excel
Related articles
How to convert matrix to vector or single column in Excel?
How to convert vector / single row or column to matrix 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