If Transpose is not showing in Excel, copy the source cells first, then select a single empty destination cell and open Home > Paste > Paste Special; choose Transpose in the dialog. If the option is missing or unavailable, check that you used Copy rather than Cut, selected one destination cell, and are not pasting into merged cells or a table; the TRANSPOSE formula is a useful alternative.
Where to find Transpose in Excel
Transpose changes the orientation of a rectangular range: rows become columns and columns become rows. In Excel for Microsoft 365 on Windows, there are two common ways to apply it as a one-time paste:
- Select the cells you want to turn around and press Ctrl+C.
- Click one empty cell where the top-left corner of the result should go. Do not select a destination block.
- On the Home tab, open the Paste dropdown in the Clipboard group and choose Transpose. Depending on the ribbon layout, you may see it as a small icon in the Paste options.
Alternatively, use Home > Paste > Paste Special, select the Transpose checkbox in the dialog, and click OK. You can also right-click the destination and look under Paste Special for a transpose option. The precise context-menu icons can differ with the Excel update and the space available in the menu, so the Paste Special dialog is the more dependable place to look.
For example, copying a 2-row by 3-column range produces a 3-row by 2-column result. Excel starts the result in the destination cell you chose and fills the required space. Make sure that space is empty and large enough; otherwise, existing content can block the paste or be overwritten.
What the Transpose command does
A transpose is more than rotating text visually. It rearranges the cells so that each original row becomes a column in the result and each original column becomes a row. If a range has 4 rows and 7 columns, the transposed range has 7 rows and 4 columns.
Paste Special Transpose creates a separate copy of the data. It is generally best when you want to reorganize a report once and then edit the new arrangement independently. Pasting this way can also carry cell formatting, unlike a formula-only solution. If the source contains formulas, Excel pastes formulas and adjusts relative references to fit their new positions. Check important formulas after transposing, particularly when they include mixed or absolute references.
Transpose does not mean sorting, reversing the order of items, or rotating a worksheet. It changes the row-and-column layout while retaining the sequence across the original rectangle. It also does not create a live link that automatically keeps the pasted copy synchronized with the source.
Why the option is missing or greyed out
The most frequent reason is that Excel does not currently have a copied cell range to transpose. The command is designed for copied cells. If you used Ctrl+X to cut the range, or clicked elsewhere after copying and cleared the moving copy border, start again with Ctrl+C. Then choose the destination and open Paste Special.
- You selected a destination range. Click just one destination cell. Excel calculates the output dimensions from the copied range.
- The source is a noncontiguous selection. A selection made up of separate blocks is not a single rectangle that Excel can transpose as one range. Copy one rectangular area at a time.
- The source or destination contains merged cells. Merged layouts can prevent a rectangular paste. Unmerge the relevant cells or use a clean, unmerged destination range.
- The destination overlaps the source. Choose an empty area that does not intersect the copied cells, especially when rearranging data on the same sheet.
- The worksheet is protected or the destination is restricted. Depending on the protection settings, Excel may block pasting into those cells. Use an allowed range or ask the workbook owner to adjust protection.
- The cells are inside an Excel table. A table has rules for its rectangular structure, and a transposed paste may not fit. Try an empty area outside the table, then decide whether the result should remain a normal range or be made into a table afterward.
If Paste Special opens but Transpose is unavailable, check the copy step and selection shape before changing workbook settings. A normal clipboard copy of one rectangular cell range is the best diagnostic test. Try copying a small, plain range to an empty worksheet area. If that works, the limitation is likely related to the original range, its destination, or worksheet protection rather than a missing Excel feature.
Use the TRANSPOSE formula instead
Use the TRANSPOSE function when the result should stay connected to the source. With the original data in A1:C2, enter this in an empty cell outside a table:
=TRANSPOSE(A1:C2)
For this example, suppose A1:C2 contains North, East, West in the first row and 12, 18, 9 in the second row. The formula spills a 3-row by 2-column result: North and 12 in the first row, East and 18 in the second, and West and 9 in the third. If a source value changes, the formula result updates.
In Microsoft 365, enter the formula once in the top-left output cell and press Enter. Excel fills the required spill area automatically. Leave those cells clear: any existing value, merged cell, or other obstruction in the spill range can cause a #SPILL! error. The formula result does not automatically copy the source cell formatting, so apply number formats, borders, or colors to the output separately if needed.
In older Excel versions that do not support dynamic arrays, TRANSPOSE may need to be entered as a legacy array formula. Select the full output range first, type the formula, and confirm with Ctrl+Shift+Enter. That procedure is not needed for a current Microsoft 365 installation with dynamic arrays. The function is also available in Excel for the web, though workbook behavior can depend on the browser version and the particular cells involved.
Fix formula errors and blocked results
If Excel accepts the formula but the result does not appear, inspect the intended output rectangle. For a source range with 2 rows and 3 columns, the output needs 3 rows and 2 columns. Clear anything in those six destination cells, check for merged cells, and move the formula if its spill area would run into a table or the edge of the worksheet.
A #SPILL! error means Excel cannot place the full dynamic result in the required cells. Select the error indicator or review the area around the formula to identify the obstruction. A formula that displays as text instead of calculating may have been entered into a cell formatted as Text, or the formula may have been prefixed with an apostrophe. Change the cell format to General, re-enter the formula, and press Enter.
Remember that the formula and the paste command solve different problems. A formula creates a live result but does not reproduce the source formatting. Paste Special creates a separate copy and is usually the easier choice for a one-time layout change. Neither method changes the original source range.
Excel tables, formatting, and data types
Structured Excel tables are designed to keep each record in a row, with consistent columns and headers. They are not always suitable destinations for a transposed shape. If you need to transpose table data, copy it to a blank area outside the table first. You can then decide whether to keep the result as a regular range or turn it into a table using Insert > Table after checking that the new first row contains suitable headers.
For a one-time paste, Transpose may bring cell formatting along with the data. Inspect the result if the original range contains merged headings, unusual number formats, conditional formatting, or formulas. When you only want values, use Paste Special and choose Values if that option is available, then apply Transpose as appropriate in the dialog. Avoid assuming that every feature attached to the original cells—such as data validation, comments, or conditional formatting—will behave exactly as it did in the source.
Windows, Mac, and Excel for the web
The instructions above use Excel for Microsoft 365 on Windows. The Windows shortcut Ctrl+C copies the selection. Ctrl+Alt+V opens the Paste Special dialog in desktop Excel; if that shortcut is unavailable on your keyboard or behaves differently in your setup, use Home > Paste > Paste Special instead. The Ctrl+V shortcut performs a regular paste, not a transpose by itself.
On a Mac, use Command+C to copy. Ribbon labels and the Paste Special dialog can vary slightly by Excel release, so look on the Home tab under Paste or use the Edit menu’s paste options. The common Windows Ctrl+Alt+V shortcut is not the Mac shortcut; use the visible menu command rather than assuming the same key combination. Excel for the web also offers paste options, but its menus and available commands can differ from desktop Excel. If the transpose option is not visible in the browser, the TRANSPOSE formula is a practical alternative.
Which method should you choose?
Choose Paste Special Transpose if you need a separate, formatted copy for a one-time report, presentation, or data cleanup. Choose the TRANSPOSE formula if the output should update when the source changes. If the result will become a table for filtering or analysis, first transpose outside the existing table, review the headers and data types, and then create a new table if that structure makes sense.
Before using either method on important data, preserve the original range or work on a copy of the workbook. This makes it easier to compare the before-and-after layout and restore information if the result is not what you intended.
Frequently Asked Questions
Why is Transpose greyed out in Excel?
Usually, Excel does not have a copied rectangular cell range ready to paste. Copy the cells with Ctrl+C on Windows or Command+C on Mac, select one empty destination cell, and check for merged cells, protection, or a table that blocks the paste.
Can I transpose data without copying and pasting?
Yes. Enter =TRANSPOSE(A1:C2) in an empty cell, replacing A1:C2 with your source range. In Microsoft 365, the result spills into the required cells and updates when the source changes.
Why does my TRANSPOSE formula show #SPILL!?
The formula needs a clear output area with the dimensions reversed from the source. Clear obstructing cells, avoid merged cells and table interiors, and ensure the output range fits on the worksheet.
Does Excel transpose formulas and formatting?
Paste Special Transpose can paste formulas and formatting, with relative formula references adjusted for the new location. The TRANSPOSE formula returns a linked array of results but does not copy the source formatting; format its output separately.
Can I transpose data inside an Excel table?
Table structure can prevent a transposed paste from fitting. Copy the data to a blank range outside the table, transpose it there, and then create a new table if you want the rearranged data in table form.
Related
- Excel Jigsaw Solver: What Works and How to Build One
- Excel Solver: Add Decision Variables and Set Constraints
- Excel Solver Binary Constraint: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Not Working? Fix the Common CausesIf VLOOKUP is not working correctly, first check that the lookup value is in the leftmost column of…
- Excel Table Not Auto Filling Formula? Fix ItIf an Excel table is not auto filling a formula, first check that the range is a real…
- Excel Circular Reference Errors: Find and Fix ThemIn Excel, a circular reference occurs when a formula depends on its own result, either directly or through…
- Excel Formula Spill Error: Causes, Checks, and FixesAn Excel #SPILL! error means a formula is trying to return results into multiple cells, but Excel cannot…
- Excel Cell List Drop-Down: Create and Fix OneTo create an Excel cell list drop-down in Microsoft 365 for Windows, select the cell, choose Data >…
- Excel SUM Not Working? Fix Common Formula ProblemsIf Excel SUM does not work, first check that the formula starts with an equals sign, covers the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.