While working on excel many times you would want to flip table rows and columns. In that case, excel provides two methods. First is the TRANSPOSE function and the 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 methods.
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 to 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 dialogue.
At the bottom-right, check the transpose checkbox. Hit the 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 the next method.
Convert rows to columns in excel using the TRANSPOSE function
If you want dynamic switching of rows and columns in excel with an existing table. Then use the excel TRANSPOSE function.
The TRANSPOSE function is a multi-cell array formula. It means you have to predetermine how many rows and columns you're gonna need and select that much area on sheet.
In the above example, we have 11x3 table in range A1:C11. To transpose we need to select 3x11 table. I selected A3:K15.
Since you 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 vice versa.
Note: This transposed table is linked with the original table. Whenever you change data in the original data, it will be reflected in the 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 an 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.
50 Excel Shortcut to Increase Your Productivity: Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the VLOOKUP Function in Excel: This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the COUNTIF function in Excel: Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use the SUMIF Function in Excel: This is another dashboard essential function. This helps you sum up values on specific conditions.
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.