To copy and transpose data in Excel for Microsoft 365 on Windows, select and copy the cells, choose the destination, then use Home > Paste > Paste Special and select Transpose. This turns rows into columns and columns into rows; for a result that updates when the original changes, use the TRANSPOSE function instead.
What transposing does in Excel
Transposing switches the orientation of a rectangular range. A horizontal list becomes vertical, a vertical list becomes horizontal, and a table is rotated across its diagonal. For example, a range with two rows and three columns becomes three rows and two columns. The order of the values is preserved; Excel changes their positions, not their meaning.
Transpose is useful when a report, chart, form, or import expects data arranged differently from the way it was collected. You might have employee names across the top of a sheet but need them down the first column, or receive monthly figures in rows when your analysis expects months in columns. Copying and transposing creates a second arrangement while leaving the source in place.
That last point matters: copying and transposing is not the same as moving or reorganizing the original cells. Excel pastes a rotated copy at the destination. If you want to replace the original layout, you will need to copy the result back over the source or remove the old range after checking the new one.
Choose between Paste Special and a formula
Use Paste Special when you need a one-time copy. The pasted result is separate from the source, so later edits to the original will not automatically update the copy. This is often the right choice for preparing a report, making a snapshot, or changing the layout of imported data.
Use the TRANSPOSE function when the destination should reflect future changes to the source. In current Microsoft 365 Excel, the function returns a dynamic array: it spills into the required number of rows and columns, and updates as source values change. The formula is linked to its source range, rather than being a detached copy.
- Paste Special: best for a fixed copy, with options for pasting formulas, values, and formatting.
- TRANSPOSE formula: best for a live result that should recalculate when its source changes.
- Power Query: worth considering for repeatable data-import and transformation workflows, rather than a quick one-off rotation.
For a simple one-time task, Paste Special is usually easier. If the original range will be refreshed or edited regularly, a formula or a repeatable data workflow can save you from doing the transpose again by hand.
Transpose a copied range with Paste Special
In Excel for Microsoft 365 on Windows, copy the source range, then open Paste Special at the destination and enable Transpose. These steps make a fixed copy of the selected range:
- Select the cells to rotate. Include the complete rectangular range, including any headers you want in the result.
- Press Ctrl+C to copy. A moving dashed border around the selection indicates that Excel has copied it.
- Select the top-left cell of the destination. Leave enough empty space for the rotated range. A source that is two rows by three columns needs a destination area three rows by two columns.
- Choose Home > Paste > Paste Special. In the dialog, select Transpose, check the other paste settings you want, and select OK. The default all-cells paste generally carries the copied contents and formatting; other paste choices let you limit what is transferred.
You can also open the Paste dropdown on the Home tab and choose its Transpose paste option when it is shown. The clipboard shortcut Ctrl+Alt+V opens Paste Special on Windows; the dialog is a reliable route if you need to choose both Transpose and a specific content type. On Mac, use Command+C to copy and Control+Command+V to open Paste Special, or use the Home tab’s Paste controls. Ribbon labels and dialog layout can vary slightly by Excel release.
After pasting, check the destination before deleting or changing the source. In particular, confirm that the first row and column ended up where expected and that no values were cut off by an occupied cell.
Transpose with the TRANSPOSE formula
For a linked result in Microsoft 365, select the top-left destination cell and enter a TRANSPOSE formula referring to the source range. For example, with the source values in A1:C2 and the desired result beginning in E1, enter:
=TRANSPOSE(A1:C2)
The source has two rows and three columns, so the formula spills into E1:F3: three rows by two columns. In the spilled result, E1 corresponds to A1, F1 to A2, E2 to B1, and so on. You enter the formula once, in the top-left cell; do not fill it across the destination manually. If you edit a source value, the corresponding value in the transposed output changes too.
Leave the cells where the result needs to spill empty. If a value, another formula, or a merged cell blocks the output, Excel may show a #SPILL! error. Clear the obstruction or move the formula to an open area. Dynamic array formulas cannot spill from inside an Excel table, so put the TRANSPOSE formula in a normal worksheet range outside the table.
To replace the formula result with an independent snapshot, copy the spilled range and paste it back using Paste Special > Values. The result will no longer update from the source. If the size of the source changes, review the formula’s output area as well; the number of rows and columns in the result follows the current source range.
Decide what to copy: values, formulas, and formatting
A transpose can transfer more than visible text and numbers. With the default all-cells paste, Excel generally carries the source cells’ formulas and formatting as well as their contents. That may be helpful when headings have fills or number formats, but it can also bring along styling that does not suit the new layout.
Use the Paste Special dialog to control the result. Selecting Values pastes the displayed results rather than the underlying formulas; selecting Formulas carries formulas without requiring the same formatting. The Transpose option can be combined with the content choice. If the output should look like the destination rather than the source, choose a paste option that omits source formatting, then format the result separately.
Be particularly careful with formulas that use relative cell references. When formulas are copied, Excel can adjust those references to reflect the new location. Check several results against the source before relying on a transposed formula copy. If you need fixed values, use Values; if you need formulas, verify that the adjusted references still point to the intended cells.
Examples of when a transpose helps
Suppose a survey export lists questions down column A and one respondent’s answers across columns B through G, but a summary sheet expects each answer on a separate row. Transposing the answer range can turn the horizontal series into a vertical list. For a one-time summary, Paste Special is quick. For a live report where responses may change, a TRANSPOSE formula can keep the second layout synchronized.
Another common case is a small table whose fields are arranged across the top when a template expects them down the side. Include the field names in the selected range if they should rotate along with the data. Leaving out a header row or column changes the shape and can make the output appear shifted, so inspect the selection before copying.
Transposing is not a substitute for sorting or reshaping data into a database-style list. If you need to filter, summarize, or append many records, a consistent layout with one record per row and one field per column is generally easier to work with. A transpose changes orientation; it does not infer relationships or reorganize categories for you.
Formatting, limits, and practical checks
Plan the destination before pasting. A transposed range uses the source’s row count as its new column count and its column count as its new row count. Any existing content in the output area can be overwritten by a paste, while occupied cells can prevent a formula’s dynamic array from spilling. Choose a blank area or a new worksheet if there is any risk of replacing useful data.
- Check merged cells: merged cells can make copying or spilling into a rotated shape difficult. Unmerge them or use a clear, unmerged destination if Excel reports a problem.
- Review number formats: dates, percentages, and currency values may need a different display format in the new layout, even when the underlying values are correct.
- Inspect widths and heights: column widths and row heights do not rotate as cell contents do. Adjust them after the paste if labels are truncated or the output is hard to read.
- Consider workbook size limits: Excel worksheets support at most 1,048,576 rows and 16,384 columns. A transposed range must fit within those limits in its new orientation.
For a formula result, check whether the destination is inside an Excel table and whether every cell in the spill area is clear. For a pasted result, check the paste mode and whether formulas, values, and formatting were carried as intended. Those simple checks catch most surprises before the transposed data is used in a chart or report.
Common problems and how to fix them
If Transpose is unavailable or the result does not appear, first confirm that Excel has an active copied range and that you are pasting into a suitable destination. Copy the range again if necessary, then reopen Paste Special. Do not use Cut when you mean to create a separate copy; copying preserves the original cells.
A #SPILL! error from TRANSPOSE usually means Excel cannot place the full result. Select the error indicator to see details, then clear the blocking cells or move the formula. If the formula is inside a table, move it to a regular worksheet range. Also check for merged cells in the intended spill area.
If the output contains unexpected values, determine whether the paste transferred formulas or their displayed values. A formula may recalculate differently after its references shift; a value paste preserves the current results but is no longer linked. If only the appearance is wrong, undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then repeat the paste with the desired formatting option. Undo is most useful before making additional edits.
Finally, remember that transposing is a rectangular operation. If you select a range with blank rows or columns, those blanks are part of its shape and appear in the rotated result. Select only the intended area, or use a formula that refers to a deliberately chosen range.
Frequently Asked Questions
Can I transpose data without copying it?
Paste Special is based on copying, so it leaves the source in place. For a linked display, use TRANSPOSE. If you want the original data itself rearranged, create and verify the transposed copy first, then remove the old range only when you are sure it is no longer needed.
Will transposed cells update when I change the source?
A Paste Special result is a fixed copy and does not update. A TRANSPOSE formula in Microsoft 365 remains linked to its source and recalculates when source values change, provided the formula and its spill area remain intact.
Can I transpose only values and not formulas?
Yes. Copy the range, open Home > Paste > Paste Special, choose Values and Transpose, then confirm. That pastes the current results rather than the source formulas. The pasted values will not recalculate when the original cells change.
Can I transpose rows and columns in Excel for Mac?
Yes. The Paste Special dialog includes a Transpose option on Mac as well. Copy with Command+C, then use the Home tab’s Paste controls or open Paste Special with Control+Command+V. The TRANSPOSE formula works in the same way.
Why does my TRANSPOSE formula show #SPILL!?
Excel cannot place the full output, usually because a cell in the required destination area is occupied or merged. Clear the spill area, move the formula to open cells, and make sure the formula is not inside an Excel table.
Related
- Bottom Border Excel Shortcut and How to Use It
- Billion Number Format in Excel: Display Values in Billions
- Birthday List Excel Template: Setup and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- HLOOKUP in Excel With Multiple Sheets: A Practical GuideHLOOKUP does not search multiple worksheets in a single lookup operation. To search several sheets, combine separate HLOOKUP…
- Excel Table Filter Multiple Columns: How It WorksYes. In an Excel table, you can filter multiple columns at the same time by choosing a filter…
- Excel LET and IF: Combine Them in Clearer FormulasLET and IF work together by assigning names to values or calculations, then using IF to choose a…
- Excel Named Range Formula: Examples and SetupA named range formula example in Excel is =SUM(Sales), where the name Sales refers to a cell range…
- Split a Cell in Excel: Columns, Formulas, and LimitsTo split text from one Excel cell into separate columns, select the column, then choose Data > Text…
- How to Add a Formula in an Excel Text Box That UpdatesTo show a formula result in an Excel text box, enter the calculation in a worksheet cell, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.