To transpose a range in Microsoft Excel, copy it, select an empty destination cell, then choose Home > Paste ▼ > Transpose. This turns rows into columns and columns into rows. For a result that updates when the source changes, enter the =TRANSPOSE(A2:D4) formula in an empty cell instead, changing the range to match your data.
Transposing changes the orientation of a rectangular range; it does not rearrange values alphabetically or rotate text. Before starting, decide whether you need a one-time copy or a live result, and make sure there is enough empty worksheet space for the transposed range.
What You’ll Need
- An Excel workbook containing the rows and columns you want to switch. The instructions below are for Excel for Microsoft 365 on Windows.
- An empty destination area. If the source has 3 rows and 4 columns, the result needs 4 rows and 3 columns.
- For the formula method, a version of Excel with dynamic arrays, such as Microsoft 365 or Excel 2021. Older Excel versions can use a legacy array formula, as explained below.
- Optionally, a saved copy of the workbook if the change is important. A pasted transpose is a separate copy, but replacing existing destination cells can overwrite data.
Step-by-Step
-
Check the source range and decide which method to use. Identify the complete rectangle you want to turn. Include the header row or column if it belongs in the result, and check whether the cells contain formulas, formatting, or only values. Use Paste Transpose for a one-time copy that you can edit independently. Use the TRANSPOSE formula when the destination should reflect future changes to the source.
For example, a range from A2:D4 has 3 rows and 4 columns. Its transposed result will have 4 rows and 3 columns. If you accidentally omit a heading or include an extra blank column, that same omission or blank column will appear in the result.
-
Copy the data for a one-time transpose. Select the source cells by dragging over the range or by clicking the Name Box to the left of the formula bar, typing the range address, and pressing Enter. Press Ctrl+C on Windows. A moving dashed border around the selected cells indicates that Excel has copied them.
Do not use Cut for this method: copy-and-paste transpose creates a destination copy and leaves the original in place. If the source contains formulas and you want the result to be fixed values, you can choose a values-only paste in the next step rather than allowing formulas to transfer.
-
Choose an empty top-left destination cell. Click a cell outside the source range where the new arrangement can begin. Check the full space it will occupy, not just the first cell. For a source with 3 rows and 4 columns, leave a clear 4-row-by-3-column area. Existing content in the destination could be overwritten, so move or save it first.
-
Paste with the Transpose option. On the ribbon, choose Home > Paste ▼ and click the Transpose option in the paste gallery. Depending on the window width, you may need to open the Paste menu to see the options. You can also right-click the destination and select the Transpose paste option if it appears in the context menu.
If you need to paste values only, open Home > Paste ▼ > Paste Special, choose Values, select Transpose, and click OK. The Paste Special dialog can present the available choices slightly differently across updates. Check the preview or resulting cells before deleting or changing the original data.
-
Verify the copied result. Compare the first and last rows and columns with the source, and confirm that each header is in the expected position. If formatting matters, inspect number formats, borders, and column widths; cell formatting may transfer, but column widths and row heights do not necessarily match the new orientation. Adjust widths or heights manually as needed.
A pasted transpose is not linked to the original range. If you later change a source value, the pasted copy does not automatically update. Keep the source as the master copy if you want to retain it, or use the formula method below for a linked result.
-
Use TRANSPOSE for a linked result instead, when appropriate. Click an empty cell where the top-left of the output should appear. If the source is A2:D4, enter
=TRANSPOSE(A2:D4)and press Enter. Excel for Microsoft 365 spills the result into the required 4-row-by-3-column area. Do not select a destination block first; enter the formula in one cell.The formula result updates when the source values change. The formula itself belongs only in the upper-left cell of the spilled result; Excel fills the rest. Keep the entire spill area clear, and do not place the formula inside an Excel Table, because dynamic array formulas cannot spill within table cells.
-
Handle older Excel versions if needed. In versions without dynamic arrays, first select an empty output range with the correct transposed dimensions. For A2:D4, select a 4-row-by-3-column destination. Type
=TRANSPOSE(A2:D4), then press Ctrl+Shift+Enter rather than Enter. Excel places the result as a legacy array formula. Do not use this multi-cell selection procedure in Microsoft 365 unless you specifically need compatibility with an older workbook or Excel version.
Common Mistakes
- Choosing too small a destination. The number of destination rows must equal the source column count, and the number of destination columns must equal the source row count. With a formula, a blocked spill area produces an error; with a pasted result, nearby content may be replaced.
- Expecting a pasted copy to stay linked. Paste Transpose makes a separate copy. Use the TRANSPOSE formula if source edits need to appear in the result automatically.
- Typing the formula into an occupied area. Excel needs a clear spill range. Check for values, formulas, merged cells, or other content in the output area, including cells that look blank but contain a formula returning an empty string.
- Assuming formulas will behave like fixed values. A normal transpose paste can carry formulas, and their references may adjust as they are moved. If you need a snapshot, use Paste Special with Values and Transpose.
- Forgetting that layout details differ. Transposed cell contents and formatting do not guarantee that column widths, row heights, print layout, or merged-cell structure will suit the new orientation. Review the output before sharing or printing it.
Tips
- Use the Name Box for large ranges. Typing a range such as A2:D4 into the Name Box can be quicker and more precise than dragging, especially when the range extends far down or across the worksheet.
- Choose a method based on how the result will be used. A pasted transpose is useful for a report or a one-off reorganization. A formula is useful for a view that should reflect changes to the source, but the output is controlled by the formula and is not edited cell by cell.
- Keep the source and output visibly separate. Add a label such as “Transposed view” above the formula result, and avoid placing the output directly over the source. This makes it easier to tell which range is original and which is derived.
- Preserve a clean backup before replacing data. If you intend to remove the original after transposing, check the output thoroughly first. For important workbooks, save a copy before replacing or deleting the source.
- On Mac or Excel for the web, use the equivalent paste option. The ribbon layout and keyboard shortcuts can differ from Windows. Look for Transpose in the Paste menu or Paste Special dialog rather than relying on a Windows-only shortcut. Excel for the web supports the TRANSPOSE function in current Microsoft 365 accounts, but the available paste options can depend on the browser and interface.
Troubleshooting
The TRANSPOSE formula shows #SPILL!: Excel cannot place the full result in the cells it needs. Clear the destination cells, unmerge any cells in the output area, and check for content that may not be obvious, such as spaces or formulas returning blank text. Then enter the formula again in the top-left output cell. If the formula is inside a table, move it to a normal worksheet range.
The formula returns only one value or does not fill the range: In Microsoft 365 or Excel 2021, enter the formula in one cell and press Enter; the result should spill automatically. If you are using an older version, select the full output range before entering the formula and confirm it with Ctrl+Shift+Enter. If the workbook is opened in a version that does not support dynamic arrays, its formula behavior may differ.
The Transpose option is missing or unavailable: Copy the source range first, then select a single empty destination cell and open the Home tab’s Paste menu. Some paste options appear only after copying cells and may not appear when the clipboard contains text from another program. If the source includes merged cells, unmerge them and try again.
The result has unexpected formulas or formatting: Undo immediately with Ctrl+Z if the paste has just happened and you want to reverse it. Copy the source again, open Paste Special, and choose the required combination, such as Values and Transpose. Review number formats and references afterward. On Mac, use Command+Z to undo; keyboard shortcuts and dialog layout differ from Windows.
Some data seems missing: Compare the source selection with the result dimensions and make sure the original range included all intended rows and columns. Check for filtered or hidden rows, blank-looking cells, and headers outside the selected rectangle. If the data is important, do not delete the original until the transposed output has been checked.
Frequently Asked Questions
Does Excel have a keyboard shortcut to transpose?
There is no single universal keyboard shortcut that directly performs a transpose paste. On Windows, copy with Ctrl+C, open the Paste menu with the ribbon or right-click, and choose Transpose. The Paste Special dialog can also be opened with Ctrl+Alt+V, where you can select its transpose option; the dialog controls may vary by Excel update.
Will a transposed range update when I change the original?
A Paste Transpose result is static and does not update. A result created with =TRANSPOSE(A2:D4) updates when the referenced source cells change, provided the formula’s spill area remains clear.
Can I transpose data inside an Excel Table?
You can copy table cells and paste a transposed copy into a normal worksheet range, but dynamic array formulas cannot spill inside a table. Put the TRANSPOSE formula outside the table, or convert or reorganize the destination range if it needs to be a table afterward.
Can I transpose only values and leave out formulas?
Yes. Copy the source, open Home > Paste ▼ > Paste Special, choose Values, select Transpose, and confirm. This puts the displayed results in the new orientation without keeping the source formulas as formulas.
Can I transpose a range back to its original orientation?
Yes. Transpose the new rectangular range again using Paste Transpose or a TRANSPOSE formula. Check its dimensions and headers first; the second operation reverses the orientation, but it will not restore content that was omitted or overwritten in the first operation.
Related
- Resize an Image in an Excel Header: Step-by-Step Guide
- Resize a Picture in an Excel Cell: Step-by-Step
- Resize Columns in Excel: Keyboard Shortcuts That Work
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Insert Image in Excel Cell With Text: A Practical GuideIn Excel for Microsoft 365 on Windows, select a cell and choose Insert > Pictures > Place in…
- How to Keep Track of Expenses in ExcelTo keep track of expenses in Excel, create a table with one row per purchase, record the date,…
- Google Sheets Auto Sum: How to Total a Column AutomaticallyTo auto sum a column in Google Sheets, select the cell where you want the total, choose the…
- Remove Duplicates in Excel: Keyboard ShortcutIn Excel for Microsoft 365 on Windows, select a cell in your data and press Alt, then A,…
- Enable Editing in Excel: Exit Protected ViewTo close Excel’s Enable Editing prompt and start editing, open the workbook and select Enable Editing on the…
- Highlight Duplicates in Excel Lists: Step-by-StepTo highlight repeated values in an Excel list, select the cells you want to check, then choose Home…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.