To transpose a range in Microsoft Excel for Microsoft 365, switch its rows and columns with the TRANSPOSE function. With source data in A1:C2, enter =TRANSPOSE(A1:C2) in an empty cell; Excel spills the result into three rows and two columns. The formula keeps the result linked to the source, so changes to the original values appear in the transposed output.
What You’ll Need
- Excel for Microsoft 365 on Windows, or a current version of Excel that supports dynamic arrays. Excel for Microsoft 365 on Mac and Excel for the web also support this formula.
- A rectangular source range, such as A1:C2, and an empty area large enough for the transposed result. A range with two rows and three columns needs space for three rows and two columns.
- Permission to edit the worksheet. If the sheet is protected, you may need the owner to unlock the relevant cells before entering the formula.
Step-by-Step
- Check the source range. Identify the first and last cells you want to rotate. The source can contain text, numbers, dates, or formulas. For a clean example, enter North, Q1, and Q2 in A1:C1, then East, 12, and 18 in A2:C2. This makes a two-row-by-three-column range. Include only the cells you want in the result; TRANSPOSE does not decide which rows or columns are headings.
- Work out the output size and choose a starting cell. Transposing reverses the dimensions: two source rows become two output columns, and three source columns become three output rows. Choose a blank cell outside the source range, such as E1, and make sure the three-by-two output area E1:F3 is clear. Leave room below and to the right in case you later expand the source and adjust the formula.
- Enter the formula in the top-left output cell. With source data in A1:C2 and E1 as the destination, type
=TRANSPOSE(A1:C2)in E1 and press Enter. In current Microsoft 365 Excel, you do not select the whole destination area first and you do not press Ctrl+Shift+Enter. Excel automatically places the result in the cells it needs. The formula in E1 is the anchor for that spilled result. - Check the transposed values. For the example above, E1:F3 should show North and East on the first row, Q1 and 12 on the second, and Q2 and 18 on the third. The values have changed position, not meaning: each source row is now a column, and each source column is now a row. If the result is too wide or tall, check that you selected the intended source range.
- Confirm the live link. Change a value in the original range, for example change B2 from 12 to 15. The corresponding transposed cell, F2, should update to 15. This is useful when the source is still being revised. Do not type directly into an individual result cell: the output belongs to the formula in E1, and Excel controls the spilled cells as a group.
- Format the result if needed. TRANSPOSE returns values and formulas, but it does not copy the source cells’ formatting, column widths, row heights, borders, or conditional formatting rules. Select the output range and apply the formatting you need using the Home tab. Adjust the destination column widths or row heights separately if labels are cut off.
- Use a fixed-size array only in older Excel versions. In Excel versions without dynamic arrays, first select an empty destination range with the reversed dimensions. For a source range of A1:C2, select a three-row-by-two-column destination, type
=TRANSPOSE(A1:C2), then confirm with Ctrl+Shift+Enter on Windows. Excel places the formula as a legacy array formula. On older Excel for Mac, use Command+Shift+Enter. Do not use this extra selection and key combination in current Microsoft 365 Excel; pressing Enter is the normal method there. - Save the workbook when the result is ready. The formula remains part of the worksheet when you save. If you need to send a fixed snapshot rather than a result that changes with the source, copy the transposed output and use Home > Paste > Paste Special > Values. Check that the pasted values are correct before removing or changing the source data.
Common Mistakes
Selecting the wrong destination size in older Excel: A source with m rows and n columns needs an output with n rows and m columns. In a current Microsoft 365 worksheet, Excel spills the correct size automatically, but a legacy array formula requires the exact destination dimensions. Count the source rows and columns before selecting the old-style output area.
Trying to type into one spilled cell: The formula is stored only in the top-left cell. The other output cells are part of the spill range, so Excel may reject edits to them. Change the source values or edit the anchor formula instead. To make a result cell independent, copy the whole result and paste values into a separate area.
Expecting formatting or layout to rotate too: TRANSPOSE changes the arrangement of cell contents, not the full appearance of the source. It will not automatically rotate column widths, row heights, borders, or every formatting rule. Apply formatting to the destination after the formula works.
Including extra cells in the reference: If you include an empty column, an unrelated heading, or a total row, those cells are included in the transposed output too. Select the exact rectangle you want to rotate. If new data is added later outside the referenced range, it will not appear unless you update the formula’s reference.
Putting the formula inside an Excel Table: Dynamic array formulas cannot spill within a table’s data area. Place the formula in an ordinary worksheet cell outside the table and ensure the spill range is clear. The source range may still be a table; it is the location of the spill result that matters.
Tips
Use TRANSPOSE when you want a live, formula-based result, such as a report that must show source data arranged in the opposite direction. Because the output is linked, it is usually better than manually retyping values that may change. Keep the source intact and make edits there, particularly if other formulas depend on it.
Make the output easy to recognize. Add a nearby note or heading that identifies it as a transposed view, and avoid placing unrelated data immediately beside or below the spill area. A clear area helps prevent accidental obstructions if the result grows.
If the source range changes size, edit the reference in the anchor formula to include the new cells. The existing formula refers to the range you specified; it does not automatically expand just because you type data next to that range. For a frequently changing dataset, consider organizing the source consistently and review the reference whenever rows or columns are added.
Remember that TRANSPOSE does not make a permanent copy. It returns the current contents of the referenced cells, and source formulas continue to calculate in the transposed view. If you need to freeze a report for sharing, paste values into a separate location and preserve the formula-based version if you may need to refresh it.
Troubleshooting
The cell shows #SPILL!: In Microsoft 365, this usually means Excel cannot place the full result in the required cells. Look for existing values, formulas, merged cells, or another obstruction anywhere in the expected spill area. Clear or move the obstruction, and keep the output range outside an Excel Table. Select the formula’s anchor cell to see the outlined spill area and Excel’s error details. Do not delete anything until you have checked that the cells do not contain information you need.
The formula appears instead of a result: Check that the entry begins with an equals sign and that the cell is not formatted as Text. Change the cell format to General, then re-enter the formula. Also check whether Show Formulas is enabled; on Windows, Ctrl+` toggles that view. If you use a keyboard layout where that key is awkward, use the Formulas tab and turn off Show Formulas. On Mac, the shortcut can differ by keyboard layout, so use the Formulas tab if needed.
The result has the wrong number of rows or columns: Select the anchor cell and inspect the range inside the parentheses. A range that is one column wider creates an output one row taller. For a rectangular source, count its rows and columns and verify that the output dimensions are reversed. Check for an accidental colon or endpoint that includes extra cells.
A value is wrong or an error appears in the output: Compare the corresponding source cell with the result’s transposed position. TRANSPOSE carries source contents across; it does not correct a typo or remove an error already present in the source. Check formulas and values in the original range. If a source cell contains a formula error, the corresponding output cell can show that error as well.
Excel rejects the array formula in an older version: Confirm that you selected the full, reversed-size output area before entering the formula, and then use the legacy array-entry shortcut for that platform. If you are using Microsoft 365, undo that approach and enter the formula in just the top-left destination cell with Enter. The dynamic-array method and the older fixed-size method are different workflows.
Frequently Asked Questions
Does TRANSPOSE update when the source changes?
Yes. In current Excel, the spilled result recalculates when a referenced source cell changes. If you paste values over the result, however, it is no longer a live formula-based output.
Can I transpose one row into a column or one column into a row?
Yes. TRANSPOSE works with a single row or a single column as well as a larger rectangular range. A one-row source spills as a column; a one-column source spills as a row.
Can I use TRANSPOSE on a range with text and numbers?
Yes. The function can return mixed cell contents, including text, numbers, dates, and formula results. It rearranges the values but does not convert text into numbers or otherwise clean the source data.
Why do blank source cells sometimes look like zeros?
Excel may display a referenced blank as 0 in a formula-generated array result, depending on the cells and workbook behavior. If blank appearance matters, test a small sample first. You can also decide whether a visible zero is acceptable for the report before relying on the output.
Can I transpose and keep the result as fixed values?
Yes. After the formula produces the output, copy the entire transposed range and use Home > Paste > Paste Special > Values in a separate destination. This keeps the displayed results but removes the live link to the original cells.
Does the formula copy cell formatting?
No. It transposes cell contents, not the source’s visual formatting or dimensions. Format the result range separately, and check its column widths and row heights before sharing or printing the worksheet.
Related
- How to Save an Excel File in Google Sheets
- How to Save an Excel File in Read-Only Mode: Steps
- Excel Rounding Shortcut: Change Decimals or Values
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Fill Shortcut: Fill Down, Right, and Series in CellsIn Excel for Microsoft 365 on Windows, select the cells to fill, including the source cell at the…
- Excel MONTH Function Examples: Formulas and TipsExcel’s MONTH function extracts the month number from a date, returning an integer from 1 (January) to 12…
- Outlook Rules: Filter Your Incoming Mail AutomaticallyCreate an Outlook rule by choosing a condition, such as who sent a message or a word in…
- Calculate Percentage in Excel: Formulas and Table TipsTo calculate a percentage in Excel, divide the part by the total, then format the result as a…
- Using SUMPRODUCT in Excel: Formulas, Examples, and Common UsesIn Excel for Microsoft 365 on Windows, SUMPRODUCT multiplies corresponding values in arrays and adds the products. If…
- XLOOKUP in Excel: What If the Return Cell Is Blank?If XLOOKUP finds a match whose return cell is truly empty, Excel normally displays 0—not a blank. With…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.