Microsoft Excel offers a range of features that allow users to work effectively and efficiently. It has unique functions to save you time. Among other things, you can transpose data in Microsoft Excel. Suppose, you have created a table with several rows and columns. Now you decide that the positions of data are not right, so you must switch rows and columns, that is transpose your data. It will be a lot of extra work to redo the table from scratch or copy/paste each data to put them in the new positions. Luckily, Excel has several options to help you with this task. Here are the different ways to transpose data in Microsoft Excel.
In your table, there are rows, columns, and numerical data. If you look at Fig. 1 you see that the rows contain months and the columns have expense and sales.
Fig. 1: Table Showing Monthly Sales and Expenses in Columns.
Now if you want to show the months as rows and the financial data as columns then you should copy the table. You can do this by dragging the mouse from the top left corner of the table to the bottom right, then right-clicking. A pop-up menu will appear and you need to select ‘Copy’. Alternatively, after selecting the table you can press ‘Ctrl+C’.
Fig. 2: Copy the data.
Then select your destination cell where you want the transposed data. After that, go to the ‘Home’ tab, ‘Clipboard’ group, and choose ‘Paste’, you will notice the ‘Transpose’ option. Clicking this option will transpose your data.
Fig. 3: Paste the data set.
Fig. 4: Transposed Data
One problem with this option is that the data of the transposed table won’t change if you change the source data as the two tables are not linked to each other.
If you want to link your source and destination table, then you should choose the ‘Transpose ()’ function. Suppose, you have 3 columns and 6 rows in your source table. Now you select 6 rows and 3 columns for your destination table. Then go to the formula bar and write ‘=transpose(‘ and select the set of data in the source table you want to transpose.
Fig. 5: Use the transpose () Function.
Fig. 6: Transposed Table.
After clicking enter, your table will be transposed. However, you will notice that the formatting of the destination table has changed. So, you need to format it again, like selecting different colors for the rows and columns, or changing the width of the cells. When handling complex data, you should be careful. The transpose formula may break.
If you want to work with complex data and don’t want your transpose formula to break, then you can use the ‘Paste Special’ option. This option will paste all the data to each cell without breaking. Also, if you change the data on the source table, the data on the new table will change. For this, you need to select the table and copy. Choose your destination cell. Then go to the ‘Paste tab’, and choose ‘Paste Special’. A dialogue box will appear. Select ‘Paste Link’. You will have the new table with transposed data.
Fig. 7: Using ‘Paste Special’ option to transpose data.
Fig. 8: Press the ‘Paste Link’ option.
Fig. 9: Transposed data.
You must remember that when you switch columns and rows, the cell address and dimensions of data will change, too. So, after transposing you should change the colors of the cell and do other styling according to your preference. All the methods mentioned here are easy and effective. You can now easily transpose your most complex data in Excel.
