127
Swapping rows with columns in Excel is very easy using a built-in function. Your tasks are limited to marking and selecting
Swap rows and columns in Excel with transpose
Sometimes you need to structure data in your Excel spreadsheet the other way around. Rows then 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 area that you want to reorganize in your Excel file. This also includes the rows and column headings. Press [CTRL]+[C] to copy the data to the clipboard.
- Select a free cell and click the right mouse button once. Make sure that you select an empty area in the table so that you do not overwrite any data.
- Click on Insert content …. Alternatively, you can click on the fourth icon from the left under Insert options:
- The Insert content window opens. Check the Transpose box and confirm with OK.
- If you have selected the symbol under Insert options:, the data area is transposed immediately. Further entries on your part are then no longer required.
- You now have a copy of the table in which the columns and rows have been swapped. Delete the original table and copy the new table to the beginning – this way you have simply swapped 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 transposing data in Excel, you convert rows into columns and vice versa. There can be various reasons why this change is necessary.
- Change structure: 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 can be easier to read if categories are in columns instead of rows.
- Consolidation of data: When data from different sources is merged, it may be formatted differently. Transposing can be used to bring this data into a uniform format.
- Avoidance of restrictions: Excel has certain limitations on the number of columns (e.g. 16,384 columns in Excel 2016). If the data comprises more columns than permitted, it can be transposed and arranged in rows that have a higher limit (1,048,576 rows).
- Pivot tables Preparation: Before creating a pivot table, it may be necessary to transpose the data. To use the correct fields as row or column labels
