Swapping rows with columns in Excel is very easy using a built-in function. All you have to do is mark and select.
Swap rows and columns in Excel using transpose
Sometimes you need to structure the data in your Excel table the other way around. Rows become columns or vice versa. This sounds more complicated than it is, because in Excel you can do this with a click of the mouse.
- Select the entire range you want to reorganize in your Excel file, including the rows and column headings. Press (CTRL)+(C) to copy the data to the clipboard.
- Select a free cell and right-click once. Make sure you select an empty area in the table so that you do not overwrite any data.
- Click on Insert content…Alternatively, you can visit Insert options: click on the fourth icon from the left.
- The window opens Insert content. Check the box Transpose and confirm with OK.
- If you click the icon under Insert options: the data range will be transposed immediately. No further input is required from you.
- Now you have a copy of the table with the columns and rows swapped. Delete the original table and copy the new table to the beginning – this way you have easily swapped the rows and columns.
Why you swap rows and columns in Excel
Transposing helps to arrange the data so that it can be used optimally for the intended analysis or presentation. When you transpose data in Excel, you convert rows into columns and vice versa. There can be various reasons why this change is necessary.
- Structural change: Sometimes you find that the current arrangement of data in rows or columns is not ideal for analysis or presentation. Transposing allows you to change the structure of the data without having to re-enter it manually.
- Readability: Certain data is easier to understand or compare in a transposed form. For example, overviews or tables may be easier to read if categories are in columns rather than rows.
- Consolidation of data: When data from different sources is merged, it can happen that they are formatted differently. By transposing, this data can be brought into a uniform format.
- Avoiding restrictions: Excel has certain limitations on the number of columns (e.g. 16,384 columns in Excel 2016). If the data has more columns than allowed, you can transpose it and arrange it in rows that have a higher limit (1,048,576 rows).
- Pivot table preparation: Before creating a pivot table, you may need to transpose your data to use the correct fields as row or column labels.
The following articles might also interest you:
- OpenOffice: Swap rows and columns
- Rotate a table in Word – here's how
- Excel: Swap X-axis and Y-axis in charts