Transpose in Excel: Flip Columns and Rows or Swap

Last updated August 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.

One clarification before the steps, because "flip" gets used for two different jobs. Transposing rotates the table so rows become columns, which is what this page covers first. Reversing, or inverting, keeps the orientation and turns the order upside down so the last row sits on top. Excel handles those with different tools, and the reverse-the-order methods are further down.

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 do I convert rows to columns in Excel?

Copy the rows, click the top-left cell of an empty area, right-click, choose Paste Special, and tick Transpose. A row running across the sheet comes back as a column running down it. This is the same operation described a dozen different ways: converting rows to columns, changing columns to rows, making rows into columns, or turning a column into a row all land on Paste Special Transpose or the TRANSPOSE function.

Direction makes no difference to the method. Transposing is symmetrical, so the same two routes handle a single column becoming a single row, a wide table becoming a tall one, or a tall one becoming wide. Pick Paste Special when you want a fixed result you can edit, and TRANSPOSE when the copy should update as the source changes.

How do I switch columns and rows in Excel?

To flip columns and rows in Excel, switching them so the headers run down instead of across, use Paste Special with Transpose ticked for a one-off, or =TRANSPOSE(A1:D10) for a live version that updates with the source. Select the whole block first, headers included, or the labels end up detached from the numbers they describe.

One thing worth separating out, because the wording overlaps: swapping the position of two columns is a completely different job from transposing. If you want the Amount column to sit before the Description column and the layout to stay as rows and columns, you are not transposing at all, you are moving a column. Moving and swapping columns in Excel covers that with Shift and drag and Insert Cut Cells. Transposing rotates the whole grid; moving a column just reorders it.

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.

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 flip data upside down in Excel?

Add a helper column numbered 1, 2, 3 down the side of your data, then sort that column Largest to Smallest. The rows come back in reverse order, last row first, and you delete the helper column afterward. In Excel 365 and 2021 you can skip the helper entirely with =SORTBY(A2:C50, SEQUENCE(ROWS(A2:C50)), -1).

The helper column exists because Excel has no built-in reverse command. Sorting needs something to sort by, and the values themselves are the wrong thing to sort on: sorting the amount column alphabetically or numerically reorders the data, it does not invert it. A sequential counter records the original order, so sorting that counter descending is the only instruction that means "give me exactly this, backwards." Select every column of your data before you sort, not just the helper, or Excel reorders one column and leaves the rest in place, silently scrambling every row.

The SORTBY version behaves differently in a way worth knowing. It spills a live result that recalculates whenever the source changes, so it is the better choice for a range you keep re-importing. SEQUENCE(ROWS(...)) builds the 1-to-n list in memory, and the -1 argument sorts it descending, which is the same logic as the helper column with nothing left to clean up.

How do I flip a column in Excel?

Reversing a single column works the same way as reversing rows: number a helper column beside it and sort descending, or use SORTBY with SEQUENCE. To flip the left-to-right order of columns instead, transpose the block, reverse it as rows, then transpose it back, since Excel's sort dialog sorts rows by default.

That left-to-right case is the one people usually mean by inverting or rotating a table, and it comes up on converted statements where the periods run oldest-to-newest across the top and you want newest first. Sorting left to right is possible directly through Data, Sort, Options, Sort left to right, but it needs a header row of sort keys and it is fiddly on wide tables. The transpose-reverse-transpose route is easier to check, because you can see the result at each step.

Here is which method fits which job.

A caution that applies to every reversing method: check whether your data has a header row before you sort. If the header is inside the selection and you did not tick "My data has headers," Excel treats it as an ordinary row and sends it to the bottom, which looks like the sort corrupted the file when it only moved one row.

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.