When you need to swap rows and columns
Many reports are built for reading, not for analysis. A monthly summary often has the months running across the top and the measures, such as Orders, Revenue and Returns, running down the side. That is easy to scan on screen, but pivot tables, charts and most import tools expect the opposite: one record per row and one field per column. Transposing turns the sheet on its side so each month becomes a row and each measure becomes a column.
How to transpose in Excel
Excel has three ways to do this. Each one suits a different situation.
Paste Special › Transpose
- Select the range, including the headers, and press Ctrl+C.
- Click an empty cell outside the selected range, for example on a new sheet.
- Choose Home › Paste › Paste Special, or press Ctrl+Alt+V.
- Tick Transpose and click OK.
This makes a static copy, so it will not update if the source changes. Some caveats:
- You cannot paste over the original range. If the target overlaps the copied cells, Excel refuses because the copy and paste areas overlap. Paste somewhere else, then delete the original.
- Excel Tables block it. Transpose is not available when you paste into a formatted Table. Use Table Design › Convert to Range first, or paste outside the Table.
- Formulas move too. Relative references shift to match their new position, which often points them at the wrong cells. Paste as values if you only need the numbers.
The TRANSPOSE function
In Microsoft 365 and Excel 2021, type =TRANSPOSE(A1:G5) in one cell and the result spills into the neighbouring cells automatically. If something is in the way you get a #SPILL! error; clear the cells it needs. In Excel 2016 and 2019 you must first select a target range of exactly the right shape (five rows by seven columns would need seven rows by five columns), type the formula and press Ctrl+Shift+Enter.
The result stays linked to the source and updates when it changes, but you cannot edit individual cells inside it. Blank source cells also show as 0 unless you wrap the range in an IF.
Power Query
Select the data and choose Data › From Table/Range. In the Power Query Editor, open the Use First Row as Headers drop-down on the Transform tab and choose Use Headers as First Row, so the current headers are not lost. Then click Transform › Transpose, and finally Use First Row as Headers to promote the old first column. Click Close & Load. This is repeatable when the source refreshes, but it is a lot of steps for a one-off job.
Where this tool helps
Monthly and quarterly reports. Finance and sales summaries with periods across the top become one row per month, ready for a pivot table or a line chart.
Survey and form exports. Some tools export one column per respondent with the questions running down the side. Transposing gives one row per respondent, which is what analysis tools expect.
Database and CRM imports. Importers read each row as a record. A sheet laid out sideways must be flipped before it can be uploaded.
Lookup tables. A small reference table with keys along the top is easier to use with XLOOKUP or VLOOKUP when the keys run down the first column.
Using the sample file, a sheet with Metric | Jan | Feb | Mar | Apr | May | Jun across the top and Orders, Revenue, Returns and New customers down the side becomes Metric | Orders | Revenue | Returns | New customers, with one row for each month. The summary reads: “Swapped rows and columns: 4 rows and 7 columns became 6 rows and 5 columns.”
How the tool handles your data
- The first column becomes the header row by default, so the labels that described each row now name each column. The top-left label (Metric in the example) stays in place.
- Types are kept. Numbers stay numbers and dates stay dates, so nothing needs reformatting afterwards.
- No overlap, Table or array formula problems. The tool builds a new sheet, so none of Excel’s paste restrictions apply.
- The size limit is explained. If the result would need more than 16,384 columns, the tool says so and suggests splitting the file first instead of failing silently.
- Formatting is not kept. Colours, fonts, borders and merged cells are left behind; the data itself is complete.
- Your file stays on your computer. Everything runs in the browser, and nothing is uploaded.
Transpose or unpivot?
Transposing is the right choice when the whole table is simply on its side. If you have several label columns, such as Region and Product, followed by one column per month, transposing would mix labels and numbers. In that case unpivoting is better: in Power Query, select the month columns and choose Transform › Unpivot Columns to get one row per region, product and month, with Attribute and Value columns. Unpivoting produces a long, narrow table; transposing only swaps the two directions.