Last updated July 2026. To transpose data in Excel, copy the range, right-click the destination cell, choose Paste Special, and check Transpose (or press Alt, E, S, E). Rows become columns and columns become rows. That is the quick, one-time method. For a result that stays linked to the source, use the TRANSPOSE function instead: in Excel 365 and 2021, type =TRANSPOSE(A1:D10) in a single cell and press Enter, and the flipped data spills automatically.
Converted PDFs sometimes arrive sideways: a report laid out with periods across the top and line items down the side, when your analysis needs it the other way around. Transposing turns that layout into the tall, single-header table that pivot tables, formulas, and filters expect, so it is a common first move after a conversion lands data in the wrong orientation.
How do I transpose data in Excel?
Select the range you want to flip and copy it with Ctrl plus C. Click the top-left cell of an empty area away from the original, right-click, and under Paste Options choose the Transpose icon, or open Paste Special and tick the Transpose box. Excel writes the data rotated: the first row runs down the first column, and the first column runs across the first row. Copy, not cut, is required here, because Paste Special Transpose does not work with cut data.
Paste into a clear space, never on top of the original range. If the paste area overlaps the cells you copied, Excel refuses with a message that you cannot overlap a copy and a paste. Once the transposed copy looks right, you can delete the original and move the result into place. This Paste Special route is the one to use when you want a static, editable snapshot that will not change if the source changes.
How does the TRANSPOSE function work?
TRANSPOSE returns a live, flipped version of a range that updates whenever the source changes. In Microsoft 365 and Excel 2021 it is a dynamic array: click one empty cell, type =TRANSPOSE(A1:D10), press Enter, and the result spills across the space it needs with no other steps. Edit a value in the source and the transposed output updates on its own, which is the whole advantage over a Paste Special snapshot.
In Excel 2019 and earlier, TRANSPOSE is a legacy array formula. You must select the exact destination range first (the mirrored dimensions, so a 4-by-10 block becomes a 10-by-4 selection), type the formula, and confirm with Ctrl plus Shift plus Enter rather than a plain Enter. Guess the size wrong and the result clips or pads with #N/A. This is the single most common reason the function frustrates people on older versions, and it is why the spill behavior in 365 is such a relief.
| Method | Result | Best for |
|---|---|---|
| Paste Special, Transpose | Static snapshot, editable | A one-time flip you will not need to update |
| =TRANSPOSE() in 365 / 2021 | Live, spills, linked to source | Data that will keep changing |
| =TRANSPOSE() in 2019 and earlier | Live, Ctrl+Shift+Enter array | Older Excel, if you preselect the range |
| Power Query, Transpose | Repeatable, refreshable step | A layout you re-import on a schedule |
What is the difference between Paste Special and TRANSPOSE?
Paste Special Transpose makes a disconnected copy; the TRANSPOSE function makes a linked one. With Paste Special, the flipped data is plain values you can edit cell by cell, and it never changes when the original does. With the function, the output is a formula result tied to the source, so it updates automatically but you cannot type over individual cells, since editing a live spill range is blocked.
Pick by whether the data will change. For a converted report you are cleaning up once, Paste Special is simpler and leaves you a normal editable table. For a source that gets refreshed or that you want kept in sync, TRANSPOSE keeps the two views matched without redoing the flip. Neither method carries over formatting: fills, bold, and number formats do not transpose, so plan to reapply them on the result.
Why won't Excel let me paste transpose?
The two usual blocks are overlap and cut. If you see "We cannot do that to a merged cell" or a message about overlapping a copy and paste area, you are pasting onto or into the range you copied; paste into an empty area instead. If Transpose is grayed out in Paste Special, you probably used Cut (Ctrl plus X) rather than Copy; only copied data can be transposed, so copy the range and try again.
Merged cells are the other snag. Transpose will not paste cleanly over or out of merged cells, and converted files often carry merged header bands. Unmerge first (Home, Merge and Center to toggle it off), then transpose. If a converted layout is full of merged headers, clearing them is usually step one, covered in the guide to merging and unmerging cells.
How do I transpose without blank cells or errors?
In Excel 365, empty cells in the source come through TRANSPOSE as zeros, which can clutter the result. Suppress them by testing each cell: =TRANSPOSE(IF(A1:D10="", "", A1:D10)) returns a blank instead of a zero where the source was empty. For a Paste Special flip, there is no zero issue, but any blank you had stays blank, so it is the cleaner route when the source has gaps you want to keep visible.
If TRANSPOSE shows a #SPILL! error, something is blocking the space the result needs to fill, usually stray data or a merged cell in the spill range. Clear the cells below and to the right of your formula and it will spill. And if the source has numbers stored as text after a conversion, they transpose as text too, so convert them to numbers before or after flipping so your totals still add up.
How do I transpose without breaking formulas?
Paste as values first, then transpose. If the range you are flipping contains formulas with relative references, a Paste Special Transpose shifts those references and usually breaks them, filling the result with #REF! errors. Copy the range, paste it back over itself as Values (Paste Special, Values) to freeze the numbers, and then do the Transpose paste on that value-only version. The flipped data holds the right numbers with no dangling references.
If you genuinely need live formulas to survive a flip, the TRANSPOSE function is the safer choice because it references the source as a block rather than carrying each cell's own formula across. For most converted files this is not an issue, since the data is plain numbers and text with no formulas yet, but it matters the moment you transpose a working model rather than a raw import.
Transposing is one fix in a larger cleanup. When a conversion drops data in the wrong shape, flipping it is step one; squaring away spaces, duplicates, and text numbers follows, all covered in the guide to cleaning up data after a PDF conversion. It helps to start from an accurate PDF to Excel conversion, and when the source is a statement, figures pulled straight into clean rows save you the reshaping in the first place.