Excel usually will not transpose data because the source was not copied as a normal cell range, or the destination contains something that prevents the pasted cells from fitting. Copy the range, choose a clear destination outside the source, then use Home > Paste > Paste Special > Transpose; if that option is unavailable, check for merged cells, a protected sheet, a table boundary, or an obstructed formula spill range.
Check the copy-and-paste basics first
Transpose swaps rows and columns: a range that is two rows by three columns becomes three rows by two columns. For the Paste Special command, Excel needs a copied cell range. Select the cells and use Copy, rather than Cut, before opening Paste Special. If you have cut the source, cancel the cut, copy it, and try again. The transpose option can also be unavailable if Excel is in cell-editing mode, if nothing has been copied, or if the clipboard holds content that is not a standard Excel cell range.
On Windows, the usual copy shortcut is Ctrl+C. On a Mac, use Command+C. You can also use the Home tab or the right-click menu, which can help distinguish a shortcut problem from a selection problem. Make sure you have selected one continuous rectangle of cells: Excel may not transpose a selection made up of separate, nonadjacent ranges.
- Press Esc once or twice to leave cell-editing mode, then copy the range again.
- Click a single empty destination cell, rather than selecting a destination shape that does not match.
- Check that the source is still outlined as copied before opening Paste Special.
- If Excel shows a message, read it before dismissing it; it often identifies the specific destination restriction.
Excel can normally determine the transposed destination dimensions from the copied range. If the source is two rows by three columns, the result needs three rows by two columns. You do not have to preselect that exact destination for ordinary Paste Special, but it must have enough room and must not overlap the source.
Use Paste Special to make a one-time transpose
For a one-off rearrangement, copy the source range, click the top-left cell where the result should begin, and choose Home > Paste > Paste Special > Transpose. In some Excel layouts, Transpose appears as a button or checkbox inside the Paste Special dialog rather than as a separate item. The right-click menu may also offer Paste Special and a transpose icon. The precise presentation varies by Excel platform and build, but the operation is the same.
Copy-and-paste transpose creates a separate result; it does not keep the new layout linked to the original. Depending on the paste option you choose, Excel can also carry over formatting and formulas. If you need plain values, use the appropriate values paste option and enable Transpose in Paste Special, where available. When formulas are included, inspect them after pasting: relative references can adjust as cells are pasted, so a transposed formula may not refer to the cells you intended.
One common misconception is that Transpose is a special command for rearranging a selected block in place. It is not. Excel cannot safely overwrite the source while changing its dimensions. Paste the transposed copy somewhere else first; after checking the result, you can remove the old range if that is what you want.
Look for destination cells that block the paste
A valid copy can still fail if Excel cannot place the result in the destination. Choose a blank area with room for the full transposed shape. The destination cannot overlap the source range, and even a seemingly empty cell may contain a formula, a space, or formatting that makes it worth checking. Clear the destination contents or try a new worksheet to find out whether the original area is the cause.
Merged cells are a frequent obstacle. A merged cell spans multiple worksheet cells, while a transposed range needs a regular rectangular arrangement. Unmerge cells in the source or destination and retry. On the Home tab, use Merge & Center to inspect the merge options; if cells are merged, choose Unmerge Cells. Unmerging can change how the existing contents appear, so check the affected area before proceeding.
A protected worksheet may prevent pasting into locked cells. If you are authorized to edit the sheet, use Review > Unprotect Sheet. A password may be required; Excel does not provide a legitimate way to bypass a password set by the workbook owner. You can also ask the owner to unlock the destination cells or provide an editable copy.
Excel tables have their own structured boundaries, which can make a paste that would extend or reshape the table fail. Try placing the transposed result in a blank range outside the table. If you deliberately need a normal range instead, select a cell in the table and use Table Design > Convert to Range, then confirm the prompt. Converting removes table behavior such as automatic expansion and structured references, so do this only if those features are no longer needed.
Use the TRANSPOSE formula for a linked result
If you want the displayed layout to update when the source changes, use Excel’s TRANSPOSE function instead of a one-time paste. In Microsoft 365 for Windows, enter a formula in the top-left cell of an empty destination area. For example, with the source labels and values in A1:C2 and the intended result beginning in E1, enter =TRANSPOSE(A1:C2) in E1. The source has two rows and three columns, so the formula spills into E1:F3, a three-row-by-two-column result. If A1:C2 contains Name, Jan, Feb on its first row and Sales, 10, 20 on its second, the result is Name/Sales, Jan/10, and Feb/20.
In current Microsoft 365 Excel, this is a dynamic-array formula: enter it once and Excel fills the required cells. Do not type into the spill area. If Excel displays a spill error, remove any content in the cells where the results need to appear, unmerge cells there, and confirm the formula is not being entered inside an Excel table. A hidden space or an existing formula can block the spill just as visible text can.
In older Excel versions without dynamic arrays, select the full output area before entering the formula. For a two-by-three source, select a three-by-two destination, type the TRANSPOSE formula, and confirm it as a legacy array formula with Ctrl+Shift+Enter on Windows. Do not use Ctrl+Shift+Enter for current Microsoft 365 dynamic-array entry. If a workbook is opened in an older version, array behavior and compatibility may differ, so check the version being used by everyone who needs to edit it.
Check merged cells, tables, and source selection
If the command is still failing, simplify the range. Copy a small, ordinary block of cells to a blank worksheet and try the transpose there. If that works, the problem is likely a feature or restriction in the original area rather than a general Excel failure. Look for merged cells, table boundaries, protected cells, hidden content, or a source selection that includes more than the intended data.
Also check that the source is a single rectangle. Selecting A1:A5 and C1:C5 together, for example, creates a multi-area selection with a gap. Transposition is designed for a rectangular block, so copy each block separately or first arrange the data into one contiguous range. If the selection includes entire rows or columns, narrow it to the actual used cells; an unnecessarily large destination can run into the edge of the worksheet or overwrite important material.
When pasting from another application, try copying the cells again from within Excel. External clipboard content may not include the cell structure required for Excel’s transpose paste option. If the source is actually text separated by tabs or commas in one cell, split it into worksheet columns or rows first; Excel cannot transpose individual items that have not yet been separated into cells.
Excel for Mac and Excel for the web
The underlying idea is the same in Excel for Windows and Excel for Mac: copy a rectangular range, select a destination, and use a Paste Special transpose option, or use the TRANSPOSE function. On Mac, use Command+C to copy rather than Ctrl+C. Ribbon and menu placement can differ by release, so look under Paste Special if the command is not in the same place as the Windows instructions. Avoid assuming that a Windows-only ribbon path or shortcut will appear identically on Mac.
Excel for the web has a different, browser-based interface and clipboard behavior can depend on the browser. If its paste menu does not offer a transpose choice, enter the TRANSPOSE formula in an empty area instead, provided the workbook supports dynamic arrays. If a formula result is blocked, clear the spill area in the web sheet or open the file in the desktop app for more complete troubleshooting. Do not confuse a browser clipboard permission prompt with a worksheet restriction; follow the browser prompt or use the ribbon copy and paste controls.
Choose between a pasted copy and a formula
The right approach depends on what you need the result to do. Paste Special is straightforward when you are preparing a report, reshaping a one-time export, or want to edit the transposed cells independently. A TRANSPOSE formula is better when the source may change and the displayed result should stay synchronized. However, formula output is not a separate editable copy: change the source cells to change the result.
- Use Paste Special > Transpose for a one-time rearrangement or when you need a static result.
- Use the TRANSPOSE function when the output should update as the source changes.
- Use a blank worksheet to test a troublesome range without risking existing data or formatting.
- Use a separate destination first if you plan to replace the original; verify the result before deleting anything.
For large or recurring data transformations, Power Query may be a better fit than repeated manual pastes. It can reshape imported data through its transformation tools, including a transpose operation, and refresh the result from the source. That is more setup than a quick paste, but useful when the same cleanup must be repeated. If you only need to swap the orientation once, Paste Special is usually simpler.
Frequently Asked Questions
Why is Transpose grayed out in Excel?
Excel may not have a copied cell range available, or you may be editing a cell, have cut rather than copied, or have selected nonadjacent ranges. Copy one continuous rectangular range and try the Paste Special menu again.
Why does Excel say the copy area and paste area are not the same size?
This often happens when the destination was preselected with dimensions that do not fit the transposed range, or when merged cells interfere with the shape. Click only the destination’s top-left cell, ensure there is room for the swapped dimensions, and remove merges if necessary.
Can I transpose data without losing the original?
Yes. Paste the result into an empty area or another worksheet. The source remains in place until you delete or overwrite it. A TRANSPOSE formula also leaves the source untouched, but its output is linked rather than independently editable.
Why does my TRANSPOSE formula show a spill error?
One or more cells in the required output area are not available. Clear those cells, check for merged cells, and move the formula outside an Excel table. The output needs a clear rectangle with the source’s row and column counts swapped.
Can I transpose a table directly?
A formatted Excel table can restrict pasting that changes its shape. Paste outside the table, or convert the table to a normal range if you no longer need its table features. Keep a copy first if the table contains important formulas or structured references.
Related
- Goal Seek in Excel: Step-by-Step Guide to Finding a Value
- Excel Goal Seek May Not Find a Solution: What to Do
- Goal Seek in Excel: How to Find a Target Value
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel NOW Format: Show the Current Date and Time CorrectlyIn Excel, enter =NOW() in a cell to return the current date and time; then format that cell…
- Hide Excel Columns Based on Cell Values AutomaticallyTo hide Excel columns when a cell contains a chosen value, use a short VBA macro that checks…
- Flip Words Upside Down in Excel: 3 Practical MethodsTo flip words upside down in Excel, use upside-down Unicode characters in a formula or paste text from…
- How to Schedule an Email to Send in Outlook: Windows & MacTo schedule an email in Outlook, write your message, open the menu beside Send, choose Schedule send, and…
- Excel Find and Replace Part of a String in a Cell or RangeTo replace part of a string in Excel, press Ctrl+H on Windows, enter the text to find and…
- Flash Fill vs AutoFill in Excel: What’s Different?Flash Fill recognizes a pattern in example data and fills in similar text, while AutoFill copies cells or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.