While working on excel many times you would want to flip table rows and columns. In that case excel provides to methods. First is TRANSPOSE function and second is paste special technique. Both have their benefits. Let us see by an example, how to convert rows to columns and columns to rows in excel using both method.
Example : Switch Excel Rows to Columns
Here I have this table of price list of some items on different dates. Currently the data is maintained column wise. I want ot switch rows to columns.
Convert rows to columns in excel using paste special
First select the entire data including headings and footers. Copy it using CTRL+C shortcut. Now right click on the cell where you want to transpose the table. Click on paste special. You can also use CTRL+ALT+V to open paste special dialog.
In the bottom right check the Transpose check box. Hit OK button.
The table is transposed. Rows are switched with columns.
Note: Use this when you want it to be done just once. It is static. For dynamic switching of columns with rows use excel TRANSPOSE function. It brings us to next method.
Convert rows to columns in excel using TRANSPOSE function
If you want dynamic switching of rows and columns in excel with existing table. Then use excel TRANSPOSE function.
The TRANSPOSE function is an multi cell array formula. It means you have to predetermine how many rows and columns you gonna need and select that much area on sheet.
In above example we have 11×3 table in range A1:C11. To transpose we need to select 3×11 table. I selected A3:K15.
Now since you have selected the range, start writing this formula:
Hit CTRL+SHIFT+ENTER to enter as multi-cell array formula. You have your table switched. Rows data is now in columns and vise versa.
Note: This transposed table is linked with original table. Whenever you change data in original data, it will be reflected in transposed table too.
Precaution: Before turning rows into columns make sure that you don’t have merged cells. With merged cell output may not be correct or may produce error.
So yeah this how you can switch table rows with columns. This is easy. Let me know if you have any doubts about this or any other query in excel 2016.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.