3 Ways to Transpose Data in Microsoft Excel

In Microsoft Excel, you can flip your data sideways, a process known as “transposing data.” Let’s say after an hour of tedious work, you created a table with multiple rows and columns. Unfortunately, you see that the data’s positions are not right. You have to switch specific rows into columns and some columns into rows. 

Instead of doing it one by one and copy-pasting data from each cell, which will take another thirty minutes of your time, you can simplify the process by transposing data. In this guide, we will mention three different ways to transpose data in Microsoft Excel. 

1. Use the ‘Paste’ Drop-Down Menu

Look at the screenshot below. 

Fig. 1: Table showing different metrics of each quarter of 2023

As you can see, the columns include information on each quarter of 2023. The rows show different metrics, including revenue, expenses, and so on. 

Let’s say you want to switch the columns with the rows in this table, as in have the metrics as the columns while the quarters (Q1 2023, Q2 2023…) are rows. In this case, you have to transpose data. One of the easiest ways to do it is by using the “Paste” drop-down menu.

Here’s how to use the “Paste” drop-down menu to transpose data in MS Excel:

1. Select the table by left-clicking the top left corner of the table and dragging the mouse to the bottom right corner. 

Figure 2: Selecting the table

1. Copy the table by right-clicking your mouse and selecting the “Copy” option. Alternatively, you can do it faster by clicking Ctrl+C on Windows and Command-C on Mac.

Figure 3: Copying the table

1. Select the cell where you want to paste the transposed table.
2. 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.

Figure 4: Transpose option under “Paste”

And there you have it! All your rows have switched to columns while the columns took the place of the rows. 

Figure 5: Transposed table

You can also use the paste function by right-clicking and selecting “Transpose” as the paste function. 

2. Use the Transpose() function

Using the “Paste” menu works to transpose a table, but the transposed table will not be linked to the original table. In this case, having a link between tables means whenever you make changes to the original table, the same changes will be applied to the modified, transposed table. 

To have that link, you need to get help from Excel functions, specifically the “TRANSPOSE()” function. Here is how to do it:

1. Select an area where there is enough space for the destination table. The source table in Figure 6 has 5 columns and 6 rows, you’ll need to select 6 rows and 5 columns in your destination area to accommodate the transposed format.
2. Go to the formula bar in your destination area, type =TRANSPOSE( and then select the range of data from your source table that you want to transpose.

Figure 6: Using the TRANSPOSE() function

1. Press Ctrl+Shift+Enter instead of just Enter. This will apply the formula as an array function, and your table will appear transposed.

Figure 7: Transposed table using the TRANSPOSE() function

Unlike using the “Paste” menu, you will notice that some formatting of the table has changed, specifically border opacity and colour fills. You will have to do them manually while using the TRANSPOSE() function, which may involve reapplying colour schemes, adjusting column widths, or making other formatting choices. Keep in mind that for larger datasets, TRANSPOSE() may require careful handling to avoid formula errors.

3. Using ‘Paste Special’

Let’s say you are working with complex data, and you can afford to have any kind of formula errors. In that case, instead of using the regular “Paste” menu or the TRANSPOSE() function, you can opt for the “Paste Special” option. 

Using this method, you’ll ensure that data is pasted accurately and that any updates to the source table are automatically reflected in the transposed table. Follow these steps:

1. Select the source table by left-clicking and dragging to cover the desired cells. Then, right-click and choose “Copy” or press Ctrl+C (Command-C on Mac).
2. Go to your destination cell where you want the transposed table to start.
3. In the “Home” tab, go to the “Clipboard” group and select “Paste Special” from the dropdown menu. 

Figure 8: Using the “Paste Special” option to transpose data

1. When the dialogue box appears, click “Paste Link” to link the transposed data to the source.

Figure 9: Clicking “Paste Link” on the dialogue box

After following these steps, you’ll have a new table where rows and columns are swapped, and any changes made to the source data will automatically update in the transposed table.

Conclusion

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 colours of the cell and do other stylings according to your preference. All the methods mentioned here are easy and effective, and if you want to learn more and become an expert on Excel, check out the online courses available on Sikkhon.