How to Switch Data in Columns and Rows in Excel
If you want to rearrange your column data to rows or vice versa, you can use the transpose feature in Excel to switch your data automatically. This method creates a static copy of your table in a new orientation, that way your changes to the transposed table will not alter the original. Follow these steps to switch your column and row data.
1. Review the Formulas in Your Table
When transposing your table, Excel automatically updates the formulas to match the new placement, as most formulas contain relative references. For example, if you copy and paste the formula =A4*B4 from cell C4 to C5, the formula will adjust to =A5*B5.
If you want the formulas to adjust, leave them as they are. If you want your formulas to maintain the original cell reference in the transposed table, you must manually change your formulas from relative to absolute before transposing.
Add the dollar sign ($) before the column and row data in the formula to make the cell reference absolute. For example, for the formula given above, you would add the dollar sign before the columns (A and B) and row (4), so it would read as =$A$4*$B$4.
2. Select and Copy the Table
Highlight all the columns and rows in the table you want to switch. Copy the table using the Ctrl + C shortcut or by right-clicking on the table and selecting “Copy.”
3. Choose a Location for the Transposed Table
Since this technique makes a static copy of your original table, your transposed table will go to a new location in your workbook. Decide where you want to paste the transposed table. Make sure the number of cells in the new area can fit all the data in your table.
4. Transpose the Data
Right-click in the top left cell of the new table area. You can find the transpose function in two places:
- “Paste Special” menu: Under the “Paste Special” menu, select “Paste Special…” In the “Paste Special” dialog, check the box next to “Transpose,” then click “OK.”
- Transpose icon: Click the transpose icon under “Paste Options” — it has two bicolor rectangles in horizontal and vertical orientations with an arrow between them.
Your table columns and rows will have switched places once the transpose function is complete.
Learn More About Our Managed IT Services
Connect with us.
Need a solution? Want to partner with us? Please complete the fields below to connect with a member of our team.