3 Methods to Transpose data from rows to columns in Excel

In this article, you will learn three different methods of transposing data from rows to columns in excel. You used to ask to transpose data in Excel for data analysis and have to transpose the data from rows to columns (and vice versa ). Sometimes you may need to transpose excel formulas instead of static values.

In graphs, you have to move from x-axis to y-axis and vice versa, which you have to do most often when creating graphs in excel.

Suppose you have sales data and ask you to create two different graphs and tables as below. It will be very easy to plot both graphs/tables with the same data if you know how to transpose data in excel. You have a toggle button to swap data Switch Row/Column for graph but for the table, you have to use any one of the transpose methods.

how to transpose data using a Keyboard ( Paste Special ), the Keyboard with the formula reference ( Paste Special ), and the Transpose array formula (Press Ctrl+Shift+Enter)

Transpose data from rows to columns using a Keyboard

1. Copy your data range C2:E12
2. Select cell H7
3. Go to Paste
4. Choose Paste Special

Keyboard short cut to open the Paste Special menu (Ctrl + Alt + V or Alt + E + S )

5. Select Values
6. Check Transpose
7. Press OK

The above steps transpose your data without format. You can format the transposed data using Format as Table.

Suppose the source data is updated. The same will not reflect in your transposed table. Since the above method, you copy and paste only the static data.

Use VBA to Transpose data

You may need a VBA script to transpose data for your report/dashboard. Use the below script to transpose data using the TRANSPOSE function in VBA

Sub CustomTransposePasteSpecial()
Range("C2:E12").Copy
Range("H7").PasteSpecial Paste:=xlPasteValues, Transpose:=True
End Sub

Transpose data from rows to columns using the Keyboard with the formula reference

To transpose data using the keyboard with the formula reference, you need to create a support table and transpose data.

Please follow the below steps to create a support table

1. Select cell C22
2. Type =$C2
3. Press Enter
4. Select cell D22
5. Type =$D2
6. Press Enter
7. Select cell E22
8. Type =$E2
9. Press Enter
10. Select range C22:E32
11. Press Ctrl+D

Your support table is ready.

1. Copy your support table data range C22:E32
2. Select cell H7
3. Go to Paste
4. Choose Paste Special

Keyboard short cut to open the Paste Special menu (Ctrl + Alt + V or Alt + E + S )

5. Select Formulas
6. Check Transpose
7. Press OK

The above steps transpose your formulas without format. You can format the transposed data using Format as Table.

Once your data is transposed you can simply delete your support table in range C22:E32. In this method, you have transposed the formula instead of static values. So if you update the value in your source table which will reflect in your transposed table.

Transpose data from rows to columns using the TRANSPOSE formula

In excel you have a built-in function Transpose(). You can use this function to transpose data from row to column and vice versa.

The Syntax of the TRANSPOSE function:

= TRANSPOSE ( Range/Array )

• Range/Array – This is the data array/table you want to transpose.

It takes only one argument Range/Array.

First, you have to choose the output range for the transposed data. you must choose a range of M rows and N columns before entering the calculation if your data consists of N rows and M columns.

For our example, You must choose a range of 2 rows and 11 columns before typing the formula since this sample data includes 2 rows and 11 columns.

1. Select cells H7:R9
2. Type below formula

=TRANSPOSE(C2:E12)

3. Press Ctrl+Shift+Enter– rather than Enter as normal.

This generates an array formula that covers the whole range that was chosen. Now that the formula is surrounded by a set of curly braces, it is clear that it is an array formula. Since they are inflexible, you will need to remove the array formula and re-enter it with a new, larger range if the source data increases.

The above steps transpose your data without format. You can format the transposed data using Format as Table.

The TRANSPOSE function in Microsoft 365

The TRANSPOSE function in Microsoft 365 is very simple to use and automatically outputs the results as an array of data. If you want the transposed results to update when the source data changes, this is a perfect solution. If the source data increases in size, it is also quite simple to update the source range in the calculation.

1. Go to cell H7
2. Type below formula

=TRANSPOSE(C2:E12)

3. Press Enter

The information will overflow into the cell to the right and down. This is a dynamic way to transpose. The transposed results will immediately update if you make any changes to the source range.

Use VBA to Transpose data

You may need a VBA script to transpose data for your report/dashboard. Use the below script to transpose data using the TRANSPOSE function in VBA

Sub CustomTranspose()
 Range("H7:R9").Value = WorksheetFunction.Transpose(Range("C2:E12"))
End Sub

Conclusion

Use the keyboard method to transpose data static/dynamic. You have the option to transpose data using the excel transpose formula.

Many times I used Keyboard with the Formula reference in my projects. It is easy and dynamically changes when the source data get updated.

Let me know in the comments if you are using any other methods.