To transpose a table in Microsoft Excel for Microsoft 365 on Windows, copy the source range, select a blank destination cell, then choose Home > Paste > Transpose. This switches rows and columns; for a result that updates when the source changes, use the TRANSPOSE function instead.
What transposing a table does
Transposing changes the orientation of a rectangular range: each source row becomes a destination column, and each source column becomes a destination row. A range that is four rows tall and three columns wide therefore becomes three rows tall and four columns wide. The cell contents move with their row and column positions, so a header row across the top will become a header column down the side.
This is useful when a report, form, or chart needs data arranged in the opposite direction. For example, a monthly report with months across the top and departments down the left may need months down the left for a different layout. Transposing changes the arrangement, not the meaning or order of the data. It does not automatically summarize, sort, or clean the information.
Excel offers two main approaches: a one-time paste and a formula-based result. Power Query is another option when the transformation should be part of a repeatable data-import process. The best choice depends on whether the result must stay connected to the original range.
Transpose with Paste Special
Paste Special is the quickest choice when you want a separate, fixed copy. In Excel for Microsoft 365 on Windows:
- Select the rectangular range you want to turn, including any headings.
- Press Ctrl+C, or select Home > Copy.
- Select the upper-left cell of a destination area that is large enough and does not overlap the source.
- Choose Home > Paste > Transpose. Depending on the ribbon layout, Transpose may appear among the paste options.
The destination takes the dimensions in reverse: a source with five rows and two columns needs an area five columns wide and two rows tall. Excel fills the transposed range from the selected destination cell. Make sure there is no important information in the cells it will occupy, because pasting can replace existing contents.
Another route is to copy the range, right-click the destination, choose Paste Special, and select Transpose in the dialog. In that dialog, you can also choose a paste type such as Values before applying the transpose. This is helpful if you want the displayed results but not the source formulas. On Mac, use the Paste or Paste Special options on the Home tab or the equivalent Edit menu; common copy and paste shortcuts are Command+C and Command+V rather than Ctrl+C and Ctrl+V.
Use the TRANSPOSE formula for linked results
A formula is preferable when the transposed view should reflect later edits to the source. With data in A1:C2, enter this formula in an empty destination cell:
=TRANSPOSE(A1:C2)
The source is two rows by three columns, so the result occupies three rows by two columns. In current Microsoft 365 Excel, the formula spills into the necessary cells automatically. If the source contains A, B, C in its first row and D, E, F in its second row, the result is A and D in the first destination row, B and E in the second, and C and F in the third. Changing a source value updates the corresponding displayed result.
Leave the spill area empty. If another value, a merged cell, or another obstruction is in the way, Excel may show a #SPILL! error instead of displaying the full result. To move or remove a dynamic result, edit or delete the formula in its upper-left cell; the other displayed cells are part of the same spilled array and cannot be edited individually.
In older Excel versions without dynamic arrays, select a destination range with the transposed dimensions first, type the TRANSPOSE formula, then confirm it with Ctrl+Shift+Enter. That creates a legacy array formula. In Microsoft 365, simply entering the formula is normally sufficient. A formula-based transpose is a view of the source, not an independent copy: changing the source changes the result, while manually editing individual result cells is not how to revise the data.
Keep values, formulas, and formatting in mind
A transpose paste normally carries cell contents and formatting. If the source cells contain formulas, Excel may adjust relative references as it copies them, just as it does with ordinary copying. Check formulas that refer to nearby cells, because the new position can change what a relative reference points to. Absolute references marked with dollar signs, such as $A$1, remain anchored.
Use Paste Special when you need control over what comes across. Transposing values is useful for a snapshot that should not recalculate. Transposing formulas can preserve calculations, but it can also bring references and dependencies you did not intend. Formatting-only details such as number formats, fills, and borders may be useful in a report, but copied formatting can also make the destination inconsistent with its surrounding worksheet.
- Choose a values paste for a static result that should not change with the source.
- Keep formulas when the destination should calculate from referenced data.
- Review number formats, especially dates, percentages, and currency, after pasting.
- Inspect totals and other formulas rather than assuming their references still make sense.
Use Power Query for a repeatable transformation
Power Query can make sense when the data is imported or refreshed regularly and the transposition is one step in a larger cleanup process. Select a cell in the source data and choose Data > From Table/Range. If Excel asks to create a table, confirm the range and whether it has headers. The Power Query Editor opens with the data available for transformation.
In the editor, choose Transform > Transpose. A table imported with headers may have those column names treated as field names rather than as a row of values. If the original header labels need to participate in the transpose, first use Transform > Use First Row as Headers > Use Headers as First Row to demote them into the data. After transposing, promote a first row to headers only if the new layout calls for it. Then choose Home > Close & Load to return the result to Excel.
Power Query produces a query result rather than a live cell-by-cell formula link. When the source changes, refresh the query to rebuild the output. If the source is an Excel Table, new rows added to that table can be included when the query is refreshed. This makes Power Query more suitable than a one-time paste for repeatable imports, but it adds a refresh step and is more setup than a simple layout change.
Choose the method that fits the job
For a one-off rearrangement, Paste Special is usually simplest. It creates a separate copy that you can edit independently. Use the TRANSPOSE formula when you want a worksheet view that responds to changes in the source. Use Power Query when the data is refreshed and transposition belongs in a repeatable import workflow.
Think about the direction of updates as well. Paste Special does not keep the destination linked to the source. A formula updates its displayed result from the source, but it is not a two-way editing arrangement: changing the output does not write back to the original cells. Power Query refreshes results from its input, so edits made directly to the query output can be overwritten on refresh. Keep the original data in a clearly identified source range or table.
Common problems and how to fix them
- The Transpose option is unavailable: Copy the source range first, then open the paste options at the destination. Paste options depend on what is on the clipboard and where you are pasting.
- The output is incomplete: Check that the destination has enough room and that no cells are merged or occupied. For a formula, inspect the cells in the spill area and clear any obstruction.
- The formula shows #SPILL!: Remove contents or merged cells from the intended spill range, then let Excel recalculate. Do not type into the cells where the array needs to appear.
- Blank cells appear as zeros in a formula result: A formula can return a zero for a blank source cell in some array contexts. If you need blanks to remain visually blank, consider a values-only paste or a separately designed formula that handles blanks.
- The result does not update: A transpose paste is a snapshot. Use a formula for a linked worksheet result, or refresh the Power Query output after its source changes.
- References changed unexpectedly: Review formulas after a transpose paste. Relative references can shift when copied; use absolute references where a reference must stay fixed.
Also check the worksheet limits before transposing a very large range. Excel worksheets have 1,048,576 rows and 16,384 columns. A transposed result must fit within those limits, so a source with more columns than the worksheet permits as rows cannot be placed as a normal transposed range.
Frequently Asked Questions
Can I transpose an Excel table without copying it?
Yes. Enter the TRANSPOSE formula in a clear destination cell to create a linked result in Microsoft 365. For a repeatable import, use Power Query. Neither method turns the output into a two-way editable copy of the source.
Does transposing change the original data?
Copy and Paste Special leave the source in place and create a second arrangement. A TRANSPOSE formula also leaves the source alone. If you want to replace the original layout, make the transposed copy first, verify it, and then decide whether to remove the old range.
Can I transpose only the values?
Yes. Copy the range, open Paste Special at the destination, choose Values, and enable Transpose. The resulting cells contain the displayed values rather than copied formulas, so they do not recalculate from the original range.
Can I transpose a range with formulas?
Yes. A paste can carry formulas, and a TRANSPOSE formula can return source values or formula results. With a paste, inspect relative references in the destination. With the TRANSPOSE function, the output is controlled by the single array formula rather than edited cell by cell.
How do I transpose data in Excel for the web?
Excel for the web includes paste options, but the exact menu presentation can differ from the Windows desktop ribbon. Look for Transpose in the paste options after copying the range. The TRANSPOSE function is also available for a formula-based result.
Before you finish
Check the transposed range against the source before sharing or deleting anything. Confirm that the intended headings moved to the correct side, that dates and numbers retain suitable formats, and that formulas or refresh steps behave as expected. For a simple one-time switch, Paste Special is efficient; for changing source data, use a formula; for recurring imports, consider Power Query.
Related
- Key Schedule Template Excel: Build One That Works
- Keep Cell Formatting in Excel When Concatenating
- Job Task List Template in Excel: How to Build and Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Inserting a Line in Word: Rules, Borders, and ShapesTo insert a quick horizontal line in Microsoft Word for Microsoft 365 on Windows, type three hyphens (—)…
- How ROUNDUP Works in Excel: Syntax and ExamplesIf A2 contains 12.341, enter =ROUNDUP(A2,2) to get 12.35: ROUNDUP moves a number away from zero to the…
- Excel Table Image: How to Insert and Manage PicturesTo put a picture in an Excel table, select a cell and choose Insert > Pictures > Place…
- How to Insert a Gray Line in Word: Shapes and BordersTo insert a gray line in Microsoft Word, go to Insert > Shapes > Line, draw the line,…
- Change Who Can View an Outlook CalendarTo change who can view an Outlook calendar, open its sharing or permissions settings, select the person or…
- How to Identify Duplicate Numbers in ExcelTo find duplicate numbers in Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.