Transpose Excel Data turns the rows of one sheet, or a range of it, into columns (and the columns into rows) and gives you the result as a new .xlsx workbook, in your browser. The typical file is a survey export with 40 questions down column A and one respondent per column. A PivotTable, a filter or a sort wants one respondent per row instead.
How to transpose Excel data with this tool
With a workbook, choose "Upload a workbook":
- Drop an .xlsx, .xlsm, .xls, .xlsb or .ods file of up to 50MB on the box, or click to browse. The file is read in your browser and never uploaded.
- If the workbook has several sheets, pick one. The list shows each sheet's size in rows × columns.
- To transpose part of the sheet, type a range: A1:D20, whole columns such as B:D, or whole rows such as 2:5. Dollar signs are fine. Leave the box empty and the tool uses the sheet's whole used range.
- Read the preview. It shows the range, the size before and after (5 rows × 3 columns becomes 3 rows × 5 columns), the top-left corner of the new sheet, and notes about any formulas, merged cells and links it found.
- Click the Transpose button, which carries the range's size (for example "Transpose 5 × 3"), then "Download .xlsx". A file called sales.xlsx downloads as sales transposed (xlsx.com).xlsx. Your original file isn't changed.
For a block of cells you already have open in Excel, choose "Paste cells":
- Select the cells in Excel and copy them (Ctrl+C, or Cmd+C on a Mac).
- Paste into the box. The page shows the size before and after and an 8 × 8 preview of the transposed block.
- Click "Copy transposed cells", click the top-left cell where they should go in Excel, and paste.
What ends up in the transposed sheet
The cell at row r, column c of your range lands at row c, column r of the new sheet, and the new sheet always starts at A1. Transpose C4:H6, three rows by six columns, and you get A1:C6. Whole columns and rows are cut to the used range, so on a sheet whose data runs A1:F240, B:D means B1:D240.
| In the source | In the new workbook |
|---|
| Numbers, text, TRUE/FALSE, error values such as #N/A | The same value |
| Number formats | Kept, so dates stay dates and 25% stays 25% |
| Notes and comments | A note with the same text, on the cell's new position |
| A link to a web page, file or email address on a text cell | Kept |
| A link to a place in the workbook, or any link on a number, date, TRUE/FALSE or error | Left out; the value stays and the page counts them |
| Formulas | The last calculated value |
| Merged cells | Unmerged, with the value in the first cell |
| Sheet name | The same name |
| 1904 date system (old Mac workbooks) | Kept, so dates don't shift by four years |
| Hidden rows and columns | Transposed like any others, and not hidden |
| Fonts, fills, borders, column widths, row heights, charts, images, data validation, conditional formatting | Not copied |
The page lists what the new workbook gets and what stays behind before you download. We opened the output in Excel for Mac: no repair prompt, the transposed values and formats in place (10% still reads 10%), and no formulas.
The download is a workbook the site builds, so its document properties name xlsx.com as author and company.
Why the formulas come out as values
Once rows and columns swap, a formula's references would point at the wrong cells. Take a budget with twelve months across B2:M2 and a total in N2 of =SUM(B2:M2). After transposing, those twelve figures run down a column. A formula that still read B2:M2 would add up a row of the new sheet that holds something else entirely.
So the tool writes each formula's last calculated value, and the page tells you how many formulas it converted and why. A formula the file has no saved result for comes out empty, and the page says that too. Some programs that export .xlsx files write formulas without results; open such a file in Excel and save it once, and the results are stored.
If you need the transposed cells to stay live, this is the wrong tool. Use TRANSPOSE in Excel, covered below.
Transposing more than 16,384 rows
It can't be done on one Excel sheet. A sheet has 1,048,576 rows but only 16,384 columns, and the last one is XFD. Transposing a 40,000-row export would need 40,000 columns.
The tool checks this before it starts. A range taller than 16,384 rows gets a message asking for one of 16,384 rows or fewer, and paste mode refuses more than 16,384 pasted rows the same way. You can transpose in blocks (rows 1 to 16,384, then the next 16,384 in a second run), but a sheet 16,000 columns wide is rarely what anyone wants. If the goal is a summary with categories across the top, a PivotTable builds it from the long list as it is: see creating a crosstab table with a pivot table.
When Paste Special > Transpose is greyed out or won't paste
Most reports of a greyed-out Transpose come down to one of these:
- The cells were cut, not copied. Transpose is only offered after Copy. Ctrl+X leaves it greyed out.
- The copy border has gone. The moving dotted border around the copied cells means Excel still holds them for Paste Special. Press Esc or type in a cell and it disappears, and Transpose goes with it. On some company laptops a clipboard or security utility clears it within a second of copying.
- The data is in an Excel table. Microsoft's own page says Transpose isn't available for table data and suggests converting it to a range first (Table Design > Convert to Range) or using TRANSPOSE.
The other common failure is "The copy area and paste area aren't the same size". It appears when you select a destination range of a different shape before pasting. Click a single cell instead.
Paste mode here needs only ordinary copy and ordinary paste, with no Paste Special step at either end. It also keeps empty cells and empty rows as they were, so nothing shifts: a gap in row 3 is still a gap in column C.
Transposing in Excel without the tool
Paste Special. Copy, right-click an empty cell, Paste Options > Transpose, or Ctrl+Alt+V, E, Enter. Pick this when you want the fonts, fills and borders to come along, which this tool doesn't do. The pasted block overwrites whatever sits in its way, so aim it at an empty sheet.
The TRANSPOSE function. Type =TRANSPOSE(A1:F12) in one cell and it spills into a 6-row by 12-column block in Microsoft 365 and Excel 2021. Older versions need you to select a range that size first and press Ctrl+Shift+Enter. The result follows the source as it changes, which neither Paste Special nor this tool does. It carries no formatting, you can't edit one cell of it, and it turns every blank source cell into 0. The fix for the zeros:
=TRANSPOSE(IF(A1:F12="","",A1:F12))
There's a longer walk-through in using TRANSPOSE to change the data layout.
Power Query. For an export you get every month, load it with Data > From Table/Range, then use Transform > Transpose and Close & Load. Transpose there drops the column headers, so run Transform > Use First Row as Headers > Use Headers as First Row before it.
Limits
- No formatting. Fonts, fills, borders, column widths, row heights, charts, images, data validation and conditional formatting stay in the original. Use Paste Special in Excel when you need them.
- Values only. There's no live TRANSPOSE option, so the result doesn't update when the source changes.
- One sheet or range per run. The new workbook holds just that one transposed sheet.
- Hidden rows and columns come out visible. Open the file in the Excel Viewer first if you're not sure what's hidden; it shows hidden rows and columns shaded.
- 16,384 rows at most, because that's how many columns a sheet has.
- Paste mode sees text. It works with the values as Excel copied them: formulas arrive as their shown results and formatting isn't on the clipboard. When you paste back, Excel reads the text as if you'd typed it, so a code like 00123 in a General cell becomes 123.
- No password-to-open files. The tool recognises one and explains why it can't read it.
A source with empty columns turns into a sheet with empty rows. Remove Blank Rows takes the downloaded .xlsx and removes them.
Questions
How do I transpose rows to columns in Excel?
Copy the cells with Ctrl+C, right-click the top-left cell of an empty area and choose Transpose under Paste Options. The keyboard route is Ctrl+Alt+V, then E, then Enter. The result is a fixed copy, so it won't follow later changes to the source.
Why is Transpose greyed out in Excel?
Usually because the cells were cut rather than copied, or because the moving border around the copied cells has gone. Transpose only appears after Copy, while that border is still showing, and pressing Esc or typing in a cell clears it. Copy the cells again and paste straight away.
How do I transpose in Excel without zeros?
Wrap the source range in IF so blank cells stay blank: =TRANSPOSE(IF(A1:F12="","",A1:F12)). TRANSPOSE on its own returns 0 for every empty cell. In Microsoft 365 and Excel 2021 press Enter; in older versions select the whole output area first and press Ctrl+Shift+Enter.
What is the difference between Paste Special Transpose and the TRANSPOSE function?
Paste Special makes a fixed copy that keeps the cells' formatting; TRANSPOSE is a formula that updates when the source changes but carries no formatting. The TRANSPOSE result is one spill range, so you can't edit a single cell of it, and it shows #SPILL! if anything is in the way.
Can you transpose more than 16,384 rows in Excel?
No. An Excel sheet has 1,048,576 rows but only 16,384 columns, ending at XFD, so 16,384 rows is the most that fit once they become columns. Transpose the data in blocks of 16,384 rows or fewer, or build a PivotTable from the long list if what you want is categories across the top.
How do I transpose data in Excel without breaking formulas?
Decide first whether you need the formulas or only their results. Microsoft's advice for Paste Special is to check that formulas use absolute references such as $A$1 before transposing, since relative references move with the cells. If you only need the results, paste as values first, or use this tool, which writes each formula's last calculated value.