Switching Rows and Columns in Excel using Transpose

The video guide shows how you can flip a range of Excel data from having headers at the top to having headers on the left by copying and pasting using the Transpose option on the Paste Special menu. The main reason for using Transpose is that you're pulling data from a source file in which the row/column layout is different to your output file. If the output file has been formatted for presentational purposes, you will only want to copy the values from your source file. This can be achieved by selecting the Values option in the Paste Special menu.

If you are using Transpose to change the layout of a pre-existing spreadsheet, your table may contain formulas. Excel transposes all relative cell references in the copied range. So if you transposed cells A1:C1 to A1:A3 and C1 originally contained the formula '=A1+B1', then A3 would end up with the formula '=A1+A2'. If your formulas contain relative references to cells outside the copied range, these will get broken as Excel will guess a new cell position as part of the transposition process. This logic applies even if the relative references are to cells on other worksheets. It would be unusual to have a formula row or column in a spreadsheet that used relative references to external cells and, if you have a formula referencing a partial absolute reference such as Sheet1!A$1, Excel won't transpose it.

It is not possible to transpose a named Excel table created as a formal Excel table as the app requires they contain data arranged in columns. It also isn't possible to use transpose with cutting and pasting. Assuming your data is not completely square, that means you will have delete some of your original data after transposing, even if you paste the top left of your transposed data in the same space it originally occupied.

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed November 2026.