Transpose Not Working in Excel? Common Causes and Fixes

If Transpose is not working in Excel, first check that you copied a cell range, then paste it into a clear destination using Home > Paste > Transpose. If you are using the TRANSPOSE formula in Microsoft 365, enter it in an empty cell and check whether a blocked spill range, merged cells, or a worksheet boundary is preventing the result.

What Transpose does in Excel

Transposing switches rows and columns. A range that is two rows high and three columns wide becomes three rows high and two columns wide. It does not sort data or reverse its order: the top-left cell remains the top-left cell of the transposed result, while the other cells change position to match the new orientation.

There are two main ways to do this in Excel. Paste Special creates a separate result that is generally a one-time copy. The TRANSPOSE function creates a formula result that can update when the source changes. Choosing the wrong method can look like a failure: a pasted copy will not automatically track later edits, while a formula may not let you type into individual cells in its output.

For example, a list of months arranged across a row can be turned into a vertical list for a report. A table with names in rows and dates in columns can also be rotated when a different layout is easier to read. Before troubleshooting, note the source range’s height and width, and decide whether the result should stay linked to the original.

Choose between Paste Special and a formula

Use Paste Special > Transpose when you need a separate copy, want to rearrange data once, or need a result you can edit cell by cell.
Use TRANSPOSE when the output should reflect changes to the source range. In current Microsoft 365 Excel, the result spills into neighboring cells automatically.
Use a normal copy and paste if the range is already oriented correctly. Transpose changes the layout, so it is not a fix for missing data or incorrect sorting.

Think about what should happen after the first transfer. If someone changes a source value tomorrow, a static pasted result stays as it was. A formula-based result recalculates from the source, provided calculation is enabled and the formula’s output range remains available.

Fix Paste Special when Transpose is missing or unavailable

In Excel for Microsoft 365 on Windows, copy the source cells with Ctrl+C, select the top-left cell of a blank destination, and choose Home > Paste > Transpose. You can also right-click the destination and choose the Transpose paste option. The source remains in place because you copied it; using Cut instead of Copy is not the right way to create a transposed copy.

Select the full rectangular source range, including any headings you want to move.
Press Ctrl+C, or choose Home > Copy.
Select a destination cell outside the source range, with enough room for the swapped dimensions.
Choose Home > Paste > Transpose, or open Paste Special and select its Transpose option.
Check the result for missing headings, unexpected formatting, and formulas that now refer to different cells.

If the option is unavailable, make sure Excel still has a copied cell range on the clipboard and that you selected a destination cell. Clicking another application or copying something else can replace the copied range. If you copied an entire row or column rather than a bounded block of cells, try copying the actual cells you need. Also check that the sheet is not protected in a way that prevents pasting into the destination.

On a Mac, use Command+C to copy, then use the Paste Special or Transpose option from Excel’s menus or contextual paste controls. Menu placement can vary by Excel for Mac version, so look for the Transpose option in the paste choices rather than expecting the Windows ribbon path to match exactly. Ctrl+C on Windows corresponds to Command+C on Mac; Ctrl+Z and Command+Z undo the most recent action on their respective platforms.

Use the TRANSPOSE formula for a linked result

In current Microsoft 365 Excel, type the function in the top-left cell where you want the new range to begin. With source data in A1:C2 and the destination beginning at E1, enter =TRANSPOSE(A1:C2) in E1. If A1:C2 contains Name, Jan, Feb in its first row and Ada, 12, 15 in its second, the result occupies E1:F3: Name/Ada in row one, Jan/12 in row two, and Feb/15 in row three.

The formula does not require you to select the full output range first in current Microsoft 365 Excel. Excel calculates the required size and spills the result into adjacent cells. If a source value changes, the transposed output updates automatically. To remove the result, delete the formula in its starting cell; the spilled cells are not separate formulas you can edit individually.

Older Excel versions may use the same function but handle it as a legacy array formula rather than a dynamic spill. In those versions, you generally select an output range with the swapped dimensions before entering the formula, then confirm it with the array-formula key combination required by that version. If a workbook is shared with people using older releases, check their Excel version before relying on spill behavior.

Resolve #SPILL! and other formula errors

A #SPILL! error means Excel cannot place the formula’s complete result in the cells it needs. Select the error cell and inspect Excel’s error indicator for details. The most common fix is to clear the cells in the intended output rectangle, not just the first cell. A space, an unseen value, or another formula can block the spill just as a visible number can.

Clear the spill area: for a two-row-by-three-column source, make room for three rows by two columns at the destination.
Remove merged cells: unmerge cells that overlap the output area, then enter the formula again.
Move the formula outside an Excel Table: dynamic array formulas cannot spill from inside a table. Put the formula in a normal worksheet range, or use Paste Special for a static result.
Check the worksheet edge: the complete transposed result must fit within Excel’s row and column limits.

Other errors point to different causes. #REF! can indicate that a referenced source cell or range was deleted. If the formula appears as text instead of calculating, check whether the cell is formatted as Text; change it to General, then re-enter the formula. Also check for an apostrophe before the equals sign. If the formula is valid but its displayed result seems old, choose Formulas > Calculation Options > Automatic, then recalculate.

Check dimensions, worksheet limits, and destination cells

Transposition swaps the range’s dimensions, which matters when the source is very large. Excel worksheets have 1,048,576 rows and 16,384 columns. A source with more than 16,384 rows would need more columns than a worksheet can hold after transposition. A destination placed too close to the bottom or right edge can also leave insufficient room, even when the complete result would fit elsewhere.

Calculate the destination size before pasting: a source with 20 rows and 4 columns needs a destination area 4 rows high and 20 columns wide. The destination’s upper-left cell determines where the result starts. If any cells in that area contain information you need to keep, move the result or clear a different area rather than overwriting them.

For a formula, the same size check applies to the spill range. Excel may show an error rather than silently dropping the cells that do not fit. For Paste Special, carefully inspect the selected destination because a paste can replace existing content in the transposed footprint.

Understand formatting and formula-reference changes

A transposed range can contain more than visible values. It may include number formats, formulas, and other cell properties. Paste Special offers different paste choices, so use the appropriate option if you want values without source formatting, or if you need to preserve formulas. After a transfer, check dates, percentages, currency, borders, and column widths: a transposed cell range does not necessarily produce a polished layout automatically.

Formulas are another reason a result can look wrong. When formulas are copied to a new location, relative references can adjust to the new position. For instance, a formula that refers to a nearby cell may point somewhere different after it is pasted. Absolute references marked with dollar signs behave differently, but do not assume every formula will produce the same result after a transpose. If you need only the displayed results, choose a values-only paste option where available, or copy the result and paste values afterward.

A formula-based transpose is linked to its source, but that does not mean you can edit one output cell independently. The output is controlled by the formula in its starting cell. If you need a freely editable transposed table, copy the result and paste values, or use Paste Special > Transpose in the first place.

Use Excel for the web and Mac appropriately

Excel for the web supports common copy-and-paste workflows, but its controls and available paste choices can differ from the Windows desktop ribbon. If you cannot find Transpose in the web interface, open the workbook in the desktop app and use Paste Special there. A dynamic array formula such as =TRANSPOSE(A1:C2) is another option in supported web workbooks, as long as the destination area is clear.

Excel for Mac also supports transposing ranges, though menus and buttons may be arranged differently from Windows. Use the Paste Special controls and look for Transpose. If a workbook behaves differently across devices, check that the apps are current and that the destination does not contain merged cells or blocking content. The source range and the intended result should be the same regardless of platform; it is usually the command location or formula support that varies.

Frequently Asked Questions

Why is Transpose greyed out in Excel?
Excel may no longer have a copied cell range available, or the selection may not be a suitable destination. Copy a bounded range with Ctrl+C on Windows or Command+C on Mac, select a destination cell, and reopen the paste options. Also check whether sheet protection restricts pasting.

Why does my TRANSPOSE formula show #SPILL!?
One or more cells in the required output area are occupied, merged, or otherwise preventing the result from spilling. Clear the complete destination rectangle, move the formula outside an Excel Table, and ensure the result fits on the worksheet.

Does Transpose update when the source changes?
A Paste Special result is a separate copy and normally does not update when the original data changes. A TRANSPOSE formula in current Microsoft 365 Excel recalculates from its source range.

Can I transpose a table and then edit each output cell?
Yes, if you create a pasted copy with Paste Special > Transpose. A dynamic-array formula controls its entire spilled output, so individual spill cells cannot be edited separately; paste the result as values if you want to edit it independently.

Why did my formulas change after transposing?
Copied formulas can adjust relative cell references to reflect their new location. Review the references after the paste, or paste values if you need fixed results rather than formulas. Absolute references can help preserve particular references, but should be set deliberately before copying.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.