This article will tell you how to transpose excel table rows and columns with menus or formulas. Then you can create another excel table based on the transposed rows and columns.
1. Example Excel Table Data.
- Below is this article’s example excel table data.
A B C 1 Sale Item Unit Price($) Sale Count 2 iPhone 1000 100 3 iPad 900 98 4 Macbook Pro 1200 300 5 Macbook Air 1300 600 6 Mac Mini 1200 96 7 Apple Watch 800 100 - If your data is in an excel table, you can first convert it to an excel cell range, please refer to the article Excel Convert Table To Range Example And Vice Versa.
2. How To Transpose Excel Table Rows & Columns With Menus?
- Select the cell range A1:C7 and right-click the cell range.
- Then click the Copy menu item in the popup menu list.
- Select a target cell and right-click it.
- Then click the Transpose(T) option under the Paste Options menu item.
- Now you can see the below excel cell range, you can see the rows and columns have been transposed.
A B C D E F G 1 Sale Item iPhone iPad Macbook Pro Macbook Air Mac Mini Apple Watch 2 Unit Price($) 1000 900 1200 1300 1200 800 3 Sale Count 100 98 300 600 96 1000 - If you can not see the Transpose(T) option under the Paste Options menu item, you can click the Paste Options —> Paste Special… —> Paste Special… menu item to open the Paste Special dialog window.
- If you want to use the cell value only ( not using the cell format ), you can choose the Values radio button in the Paste group.
- If you want to use the source cell format and values, you can select the radio button All or All using Source theme.
- Then check the check box Transpose at the bottom right corner of the Paste Special dialog window.
- Click the OK button to get the transposed cell range.
3. How To Transpose Excel Table Rows & Columns With Formulas?
- You can also use a formula with the excel TRANSPOSE function to transpose excel table rows & columns.
- Input the formula =TRANSPOSE(A1:G3) in a cell and press the enter key, then it will transpose the rows and columns in the cell range A1:G3.