3 Ways to Transpose Data in Microsoft Excel

3 Ways to Transpose Data in Microsoft Excel

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.

Use the ‘Paste’ drop-down menu

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.

transpose table

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’.

Transpose Table

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.

Transpose Table

Fig. 3: Paste the data set.

Transposed data

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.

Use the Transpose () function

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.

transpose function

Fig. 5: Use the transpose () Function.

transpose data

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.

Using ‘Paste Special’

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.

transpose data

Fig. 7: Using ‘Paste Special’ option to transpose data.

transpose 8

Fig. 8: Press the ‘Paste Link’ option.

transpose data

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.

Payment Platform



Rize Capital Ltd
Registered in England and Wales.
Registration # 12039916
53 Rodney Crescent, Filton,
Bristol BS34 7AF
United Kingdom

Copyright © 2019 – 2024  Rize Capital Ltd

Contact Support