Yes. In Excel for Microsoft 365 on Windows, copy the cells, select an empty destination, then choose Home > Paste > Transpose. For a result that updates when the source changes, enter a TRANSPOSE formula instead; the right method depends on whether you need a one-time rearrangement or a live link.
What transposing cells means
Transposing switches a range’s rows and columns. A row of five quarterly figures becomes a column of five figures, while a table with three columns and two rows becomes a table with two columns and three rows. The values keep their order: the first source row becomes the first destination column, and the next source row becomes the next destination column.
Transpose is not the same as sorting, rotating, or reversing data. It changes the layout, not the sequence within each row or column. It can be useful when a report expects months down the side but your source has months across the top, when you need to turn a list of field names into headers, or when a chart or analysis works better with series arranged in the other direction.
Transpose a range with Paste Special
Paste Special is the straightforward choice when you want a separate, editable copy that will not keep updating from the original. In Excel for Microsoft 365 on Windows:
- Select the source range and press Ctrl+C.
- Click a blank cell where the top-left corner of the transposed result should go. Leave enough empty space for the result, with the source’s row and column counts switched.
- On the ribbon, choose Home > Paste > Transpose. Depending on the ribbon layout, Transpose may appear in the Paste dropdown or its paste-options icons.
- Check the result before deleting or changing the original.
You can also right-click the destination and choose the Transpose paste option. Another route is Home > Paste > Paste Special, then select Transpose in the dialog. The dialog is useful when you want to control what is pasted as well as its orientation. If you use the keyboard, Ctrl+C copies on Windows; Ctrl+Alt+V opens Paste Special in many Windows Excel configurations, where you can select the Transpose option. The ribbon or context menu is easier to follow if your dialog differs.
The source remains in place when you copy and paste. That makes this a relatively safe way to reorganize a range: you can compare the result with the source, then remove the original if appropriate. Do not assume that every paste option brings along the same contents. The default paste generally includes the copied cells’ contents and formatting, while Paste Special lets you choose options such as values, formulas, or formats. Choose deliberately if you do not want the original formatting or formulas.
Use the TRANSPOSE function for a live result
A formula is better when the transposed range should reflect later changes to its source. In Microsoft 365, select the cell where the result should begin and enter the formula below. Here the source data occupies A1:C2, so the result will occupy two columns and three rows.
=TRANSPOSE(A1:C2)
Press Enter. In current Microsoft 365 Excel, the formula returns a dynamic array and spills into the required neighboring cells. You normally enter the formula only in the top-left result cell; do not fill it across or down. Edit the source and the displayed result updates. The formula cell is the anchor for that result, so deleting it removes the whole spilled output.
Dynamic array results need an unobstructed destination. If a cell in the expected spill area already contains data, Excel displays a #SPILL! error rather than overwriting it. Clear or move the blocking cells, and check that the destination is not inside an Excel Table: spilled array formulas cannot spill within a table. Also confirm that the transposed dimensions fit on the worksheet. A sheet has 1,048,576 rows and 16,384 columns, so a range that would require more columns or rows than those limits cannot be placed there.
The formula keeps a connection to the source, but it does not reproduce the source’s cell formatting. Apply number formats, fills, borders, or alignment to the result separately. A formula-based result is also not a free-standing copy: if you send or move the result without its source, you may need to replace it with values or include the referenced cells.
Choosing between a pasted copy and a formula
Choose Paste Special when the rearranged data is a snapshot, when you need to edit the transposed cells independently, or when you want to place a result in a workbook without a continuing formula link. Choose TRANSPOSE when the source may change and you want the new layout to track those changes automatically.
- Use Paste Special for a one-time report, an independent copy, or a layout where manual edits to the result are expected.
- Use TRANSPOSE for a dashboard or working sheet that should stay synchronized with a source range.
- Consider a Power Query transformation when the data is imported repeatedly and you want a repeatable reshape process rather than a manual paste.
There is a practical distinction between a linked formula result and a copied result. With TRANSPOSE, changing an input cell changes the output. With a pasted copy, changing the source does not update the copy. Conversely, a formula result is not usually the best place to type over individual returned cells: those cells belong to the spilled array. If you need to customize particular output cells, make a pasted copy or adjust the source or formula instead.
What happens to formulas, blanks, and formatting
When you use Paste Special with the default settings, Excel transposes the copied cell content and normally its formatting. If you choose Values, it pastes the displayed results rather than the source formulas. If you choose Formulas, formulas are copied, and relative references can adjust to the new destination just as they do in ordinary copying. Review formulas after a transpose if their references are important; a changed layout can make a formula point somewhere different from what you intended.
TRANSPOSE returns an array based on the referenced range. If the source cells contain formulas, the displayed results are returned through the array, and the output recalculates when its source values change. It does not copy the source’s visual styling. Empty source cells appear blank in the transposed output, although formula results and genuinely empty cells can behave differently in some later calculations. If blank-versus-zero behavior matters to a report, test the result with the formulas that will use it.
Neither method automatically creates a new Excel Table in the transposed orientation. A pasted range can be converted to a table afterward, provided its layout meets table requirements, such as having a header row. A dynamic array formula cannot spill inside a table, so put the formula outside the table or use another workflow.
Transpose on Mac and Excel for the web
Excel for Mac also provides a Transpose paste option, usually through the Home tab’s Paste menu or the destination’s context menu. The labels and exact arrangement of options can vary by release. To copy on Mac, use Command+C rather than Windows Ctrl+C; the transpose operation itself is available through the paste options. Microsoft 365 for Mac supports dynamic arrays, including TRANSPOSE, though the ribbon presentation can differ from Windows.
Excel for the web has paste options and supports dynamic array formulas in current versions. If you do not see a Transpose icon in the paste menu, use the TRANSPOSE formula or open the workbook in the desktop app. Browser controls, account features, and the workbook’s format can affect which commands appear. For a formula, enter it in an empty cell and allow the result to spill, just as you would in current desktop Excel.
Common problems and how to fix them
- The result overwrites or collides with existing work: Paste Special may replace destination contents. Choose a truly empty destination and leave room for the switched dimensions.
- TRANSPOSE shows #SPILL!: Clear cells in the spill area, unmerge cells that obstruct it, and move the formula outside any Excel Table.
- The result does not update: A Paste Special result is a copy, not a live link. Use a TRANSPOSE formula if it should follow source changes.
- The output looks different: A formula does not carry formatting. Apply the desired formats to the result; with Paste Special, choose the appropriate paste options.
- A formula points to unexpected cells: Review references after pasting formulas. Relative references may shift as the content moves; use an appropriate absolute reference in the source formula when its reference must stay fixed.
For a large or important range, make a copy of the worksheet or workbook before changing its structure. Check headers, totals, formulas, and any charts or named ranges that depend on the original layout. Transposing a range does not necessarily redesign dependent reports or charts to match the new orientation.
When Power Query may be a better alternative
If the same data arrives in a new file every week or month, repeating a copy-and-paste operation can be error-prone. Power Query can reshape imported data as part of a refreshable query. Its transpose transformation is most helpful when the input structure is consistent and you want to repeat the same steps on updated data. The exact query steps depend on how the source is organized, and the result is loaded as query output rather than as a live TRANSPOSE formula.
For a single small range, Paste Special or TRANSPOSE is usually quicker. For repeated imports, Power Query can make the process more consistent, but it takes initial setup and may be unnecessary for a one-off task. If the goal is only to switch rows and columns once, there is no need to use a more elaborate tool.
Frequently Asked Questions
Can I transpose cells without changing the original data?
Yes. Copy the source and paste it using the Transpose option into a different location. The original remains unchanged. A TRANSPOSE formula also leaves the source alone, but its output stays linked to it.
Can I transpose data and keep the formatting?
Yes. Paste Special with the normal all-content option generally carries formatting along with the cells. A TRANSPOSE formula returns the data but does not reproduce formatting, so format the output range yourself.
Does transposing a range update automatically?
Only if you use a formula such as TRANSPOSE. A result made with Paste Special is a separate copy and will not change when the source is edited.
Can I transpose a single row or column?
Yes. Copy a row and paste it with Transpose to make a column, or copy a column and transpose it into a row. The number of entries stays the same; only their orientation changes.
Why can’t I transpose my data into the cells I selected?
The destination may not have enough room, may contain cells that would be replaced, or may intersect a spilled formula or merged cells. For TRANSPOSE, an occupied spill area causes #SPILL!, and a dynamic array cannot spill inside an Excel Table. Choose a clear, adequately sized destination.
Related
- Can You Capitalize Words in Excel? Methods That Work
- Can You Align Shapes in Excel? A Practical Guide to Layout
- Can Power Automate Refresh Excel Power Query?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Meal Planning Template Excel: Free Options and SetupYes—free meal-planning templates for Excel are available through Microsoft’s template gallery and other download sites, and you can…
- How to Clear Cache in New Microsoft Outlook on WindowsTo clear cached app data in New Outlook for Windows, close Outlook, then open Windows Settings and choose…
- Microsoft Word Bullet Hanging Indent: Set It CorrectlyIn Microsoft Word, select the bulleted paragraphs, then go to Home > Paragraph dialog launcher > Indents and…
- Debt Snowball Spreadsheet: Build a Payoff PlanTo make a debt snowball spreadsheet in Excel, list each debt’s current balance, APR, and minimum payment, then…
- CSV File to Excel Converter: Convert Without Data LossTo convert a CSV file to an Excel workbook in Microsoft Excel for Microsoft 365 on Windows, select…
- Excel Dropdown List Color Change: What You Can Do InsteadExcel cannot change the color of individual choices inside a standard data-validation dropdown. You can, however, use conditional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.