How to transpose rows to columns in Excel

Method 1: Paste Special > Transpose

The sheet has regions down the side and months across the top, 4 rows by 5 columns. A chart or a report wants months down the side instead. For a one-off flip, Paste Special is fastest.

1. Select A1:E4 and press Ctrl+C. Copy, not cut: Transpose is not available after Ctrl+X.
2. Click an empty cell outside the copied range, for example A7. The pasted block will be 5 rows by 4 columns, so make sure that area is clear.
3. Right-click and choose the Transpose icon under Paste Options, or Home > Paste > Transpose. The keyboard route is Ctrl+Alt+V, tick Transpose, then Enter.
4. Delete the original block if you no longer need it.

Paste Special Transpose does not keep links

The pasted copy is static. Change a sales figure in the original and the transposed copy does not change. In the Paste Special dialog, the Paste Link button is not available while Transpose is ticked, so you cannot get both from the dialog.

Pasting also copies formats but not column widths, so the new month column may need widening. If the source holds formulas rather than values, check them after pasting: Excel adjusts the references inside them, and ones pointing outside the copied block may no longer point where you expect. Paste as values first (Paste Special > Values with Transpose ticked) when you only need the numbers.

For a flipped copy that updates, use a formula.

Method 2: the TRANSPOSE function (live)

In Excel 365 or 2021, type =TRANSPOSE(A1:E4) in one empty cell and press Enter. The result spills into 5 rows and 4 columns, and it updates whenever the source changes. You can only edit the first cell of the spill; to remove it, delete that cell.

In Excel 2019 and older, TRANSPOSE is an array formula: select the full destination first (5 rows by 4 columns), type =TRANSPOSE(A1:E4) and press Ctrl+Shift+Enter. Excel shows it with curly braces, and you can only change or delete the whole block at once.

If something is in the way, the 365 version shows #SPILL! instead of a result. Click the error icon and Select Obstructing Cells to see what is blocking it, then clear it. TRANSPOSE also gives #SPILL! inside an Excel table, because tables do not allow spilled results; put it in a plain range.

Gotcha: blank cells turn into 0

The South store has no March figure, so D3 is empty. A formula that returns a reference to an empty cell shows 0, and that applies to TRANSPOSE and to the INDEX method below. A 0 in a sales report reads as "sold nothing", which is not the same as "no data".

Add &"" to show a blank instead: =TRANSPOSE(A1:E4&""). The catch is that &"" turns every number into text, so totals and charts stop working. When the numbers matter, use =TRANSPOSE(IF(A1:E4="","",A1:E4)), which keeps numbers as numbers and leaves only the empty cells blank.

Method 3: INDEX for any version, no array formula

If you are on an older Excel and do not want a locked array block, INDEX can transpose one cell at a time. In the top-left cell of the destination, type =INDEX($A$1:$E$4,COLUMN(A1),ROW(A1)) and fill it right and down over a 5-row by 4-column area.

It works because the destination's column number becomes the source's row number and the other way round. COLUMN(A1) and ROW(A1) must stay relative, and the source range must be absolute. Fill too far and INDEX runs off the end of the source and returns #REF!, which tells you where to stop.

Turn one row into one column

For a single row, the formula is shorter. =INDEX($B$2:$E$2,ROW(A1)) filled down four rows lists North's months as a column: 4200, 3900, 4650, 5100. In Excel 365 or 2021, =TRANSPOSE(B2:E2) does the same in one cell.

Which method to pick

Paste Special > Transpose for a one-off copy, such as reshaping data before sending it. TRANSPOSE in Excel 365 or 2021 when the flipped table must follow the source. INDEX with COLUMN and ROW when the file is used in Excel 2019 or older and you want ordinary cells you can edit individually. If you are reshaping data regularly, Power Query (Data > From Table/Range) has a Transpose command too, and you refresh it instead of repeating the steps.