Sometimes you might have a worksheet with data listed in columns, and you want to transpose (rearrange) it into rows, or vice versa.
- Select the data you want to rearrange, including any row or column labels, and press Ctrl+C.
Note You have to use the Copy command to rearrange the data, so make sure you didn’t accidentally use the Cut command.
- Right-click the first cell where you want the rearranged data to be pasted, and then under Paste Options, choose Transpose .
Note Be sure to pick a spot in the worksheet that has enough room to paste your copied data. It can’t overlap with any data that’s already there
- Once the data is transposed, you can delete the original data that you copied.
This method can save you from rebuilding an entire worksheet piece by piece. Say, for example, you have data that looks like this, with sales regions listed along the top and the quarters listed along the left side….
You can use the method above to list quarters along the top and the regions along the side, like this….
If your data doesn’t transpose the way you wanted, check these:
- Did you use the Copy command to rearrange the data? If you tried the Cut command, it won’t work.
- Did the transposed cells include formulas? Excel automatically updates the formulas to match the rearranged data. If for some reason your formulas aren’t working correctly after you transpose data, check to see if the formulas use absolute references. If they don’t use absolute references, you’ll need to undo your transposed data and Switch between relative, absolute, and mixed references.
If you have a lot of data to transpose, it might be faster to TRANSPOSE function.