Typed your data down a column but need it across a row, or the other way around? Excel calls this transposing, and you don’t need to retype anything. You can flip data from vertical to horizontal with Paste Special in a few seconds, or use the TRANSPOSE function to keep the flipped copy linked to the original. These steps work in Excel 2013 and every newer version, including Microsoft 365.
Quick Answer
Select and copy the cells (Ctrl + C), click the cell where you want the flipped data to start, then go to Home > Paste (arrow) > Transpose. Or right-click the destination and choose the Transpose icon under Paste Options. Columns become rows and rows become columns.
Method 1: Paste Special > Transpose
- Select the range you want to flip, such as A1:A5, including headers.
- Press Ctrl + C to copy. (Use Copy, not Cut; Transpose isn’t available after Cut.)
- Click the first cell of an empty area with enough room, for example C1. The pasted range can’t overlap the copied range.
- On the Home tab, click the arrow under Paste and choose Transpose (the icon with two arrows). Or click Paste Special, check Transpose, and click OK.
- Delete the original range if you no longer need it.
Keyboard shortcut: After copying, select the destination and press Ctrl + Alt + V to open Paste Special, press E to check Transpose, then press Enter.
Paste Special > Transpose creates a static copy. Formulas are adjusted, formatting comes along, and changes to the original won’t update the copy.
Method 2: The TRANSPOSE Function (Linked Copy)
Use TRANSPOSE when you want the flipped data to update whenever the original changes.
Excel 365 and Excel 2021 or later
- Click an empty cell.
- Type =TRANSPOSE(A1:A5) and press Enter.
The result “spills” automatically into as many cells as needed. If you see #SPILL!, something is in the way; clear those cells.
Excel 2013, 2016, and 2019
- Count the source cells. A1:A5 is 5 rows by 1 column, so the result needs 1 row by 5 columns.
- Select exactly that many empty cells, for example C1:G1.
- Type =TRANSPOSE(A1:A5).
- Press Ctrl + Shift + Enter. Excel wraps the formula in curly braces { }, showing it’s an array formula.
In these versions, you can’t edit or delete one cell of the array. Select the whole result range to change it. TRANSPOSE doesn’t copy formatting, and blank source cells show as 0.
Method 3: Paste Transposed Values Only
To flip data but drop formulas and formatting, open Paste Special, choose Values, and also check Transpose, then click OK.
Method 4: Power Query (Large or Repeating Data)
For a table you refresh often, select it, go to Data > From Table/Range (in Excel 2013, install the free Power Query add-in first), then in the Power Query Editor choose Transform > Transpose and Close & Load. Refreshing reruns the transpose on new data.
Common Problems
- Transpose is grayed out: You used Cut instead of Copy, or you’re pasting from another app with limited formatting. Copy again in Excel.
- “The copy area and paste area aren’t the same size”: Or “overlap” errors. Choose a destination that doesn’t overlap the source.
- Formulas show wrong results after transposing: Relative references shift. Use absolute references (like $A$1) in the source if they should point to the same cells, or paste Values.
- Merged cells cause errors: Unmerge cells before transposing.
- Too many columns: Excel has 16,384 columns. More rows than that can’t be transposed into one row.
- Tables: You can’t paste transposed data into an Excel Table, and you can’t transpose a Table’s structure directly. Convert it to a range first (Table Design > Convert to Range) or paste to a regular area.
Frequently Asked Questions
What does transpose mean in Excel?
It swaps rows and columns: the first column becomes the first row, the second column becomes the second row, and so on.
Can I transpose in Excel on a Mac?
Yes. Copy with Command + C, then choose Edit > Paste Special (or Control + Command + V), check Transpose, and click OK.
Does this work in Google Sheets?
Yes. Use Edit > Paste special > Transposed or the =TRANSPOSE() function.
Summary
- Copy the cells with Ctrl + C.
- Click an empty destination cell.
- Choose Home > Paste > Transpose (or Ctrl + Alt + V, E, Enter).
- Use =TRANSPOSE() for a copy that stays linked.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.