How to Transpose Rows to Columns in Excel (5 Useful Methods) -

Transposing data in MS Excel is truly one of the most common tasks performed by Excel users. Indeed, there are many ways to transpose rows to columns in MS Excel. In this article, we will show several easy and effective methods with suitable examples and proper explanations.

The following image shows what we are actually doing in this article.

Table of Contents hide

Download Practice Workbook

5 Useful Methods to Transpose Rows to Columns in Excel

1. Use Paste Special Command and Link to Transpose Rows to Columns

1.1 Using Paste Special Command

1.2 Transpose a Table and Link It to Initial Data

2. Use TRANSPOSE Function to Invert Rows to Columns

2.1 Use TRANSPOSE Function as a Dynamic Array

2.2 Use TRANSPOSE Function with Ctrl+Shift+Enter

3. Use INDIRECT & ADDRESS Functions to Transpose Rows to Columns

4. Use Excel Power Query Tool to Transpose Multiple Rows to Columns

5. Use of Excel VBA Macros to Convert Rows to Columns

Conclusion

Download Practice Workbook

You can download the practice book from the link below.

Rows to Columns Inversion.xlsm

5 Useful Methods to Transpose Rows to Columns in Excel

Assuming that we have a data set that presents the year-wise results of some students. We want to transpose the rows of this data set to columns for the sake of better presentation. In the following sections, we will show 5 instrumental excel methods to perform this task.

1. Use Paste Special Command and Link to Transpose Rows to Columns

We can use the simple Paste Special command in two ways described below.

1.1 Using Paste Special Command

This method is quick and easy, but it’s not suited for repeated processes. However, let’s see how this method works.

Steps:

First of all, select the range of cells (B4:I9) you want to transpose.
Then right-click and Copy. You can also use the keyboard shortcut Ctrl+C instead.

Choose the cell location (B11) where you finally want the output.
Go to Paste > Paste Special.

The Paste Special menu will open up. Mark the Transpose checkbox.
Alternatively, You can also open the Paste Special menu using the keyboard shortcut Ctrl+Alt+V.

Press OK.

So, we have generated the transposed data set from our initial data set.

1.2 Transpose a Table and Link It to Initial Data

A drawback of the method above is due to a change in any data in the initial data set, it won’t change in the transposed data set. So, we need a method that’ll link up the output with the initial data set and will update accordingly. Following the steps below, let’s see how to do this.

Steps:

Select your desired range of cells (B4:I9) > Ctrl+C > Select the desired location (B11).
Go to the Paste Special menu by pressing Ctrl+Alt+V > Click on Paste Link.

The new data set is now at the cell range of B11:I16. It’s not transposed yet. We have just linked it up with our original data set. Along with that, we see that the format is also gone. You have to redo the format unless you want it gone!

Open Excel’s Find and Replace dialog box. Or, press Ctrl+H instead and get to the Replace Tab directly.
Replace all “=” characters with “abc” or any letters that are not present in the data set > Click on Replace All.

It may seem that we have lost our new data set! We’re three more steps behind our desired transposed data table.

Select our new data set and press Ctrl+C > Select a location (K4) to place the output finally.
Press Ctrl+Alt+V > Mark the Transpose checkbox.

Press OK.

Select the table at K2:P9 > Press Ctrl+H and get to the Replace Tab.
Replace each “abc” by “=” > Press Replace All.

Finally, we have generated our desired linked-up and transposed data set.

It should be noted that if you change any input in the original data set, the transposed data will also change accordingly. Look at the screenshot attached here.

2. Use TRANSPOSE Function to Invert Rows to Columns

The TRANSPOSE function is very easy to use as a dynamic array in MS Excel 365. You can use it in other versions as well in a bit different way. We will show that in this section.

2.1 Use TRANSPOSE Function as a Dynamic Array

Steps:

Select the location (B11) where to place your desired output in the end.
Type the following formula selecting your entire data set.

=TRANSPOSE(B4:I9)

Then press Enter.

Finally, we’ve generated the transposed data set, but lost our data set format.

It should be noted that the TRANSPOSE function is dynamic which means if you change anything in the original data set, it will automatically update in the transposed output.

2.2 Use TRANSPOSE Function with Ctrl+Shift+Enter

If you don’t have MS Office 365 and have to work with the older versions of MS Excel instead, you can still work with the TRANSPOSE function. But this time it’ll be a bit less flexible for you.

In this case, you’ve to count first how many columns and rows are in the original data set.

The general rule is that if the input data set has X rows and Y columns, the output data set will have Y rows and X columns.

In this case, our example has 8 columns and 6 rows in total.

Steps:

Select a random range of cells having 6 columns and 8 rows.
Type the following formula:

=TRANSPOSE(B4:I9)

Press Ctrl+Shift+Enter or, simply press Enter if you are an MS 365 user.

Finally, we have got the transposed data set. Look at the formula bar. The curly braces indicate that this is an array formula.

Similar Readings

3. Use INDIRECT & ADDRESS Functions to Transpose Rows to Columns

We can also transpose rows to columns in two steps by combining the INDIRECT and ADDRESS functions.

Steps:

Firstly, Select the cell location (B11) where you finally want to place the output.
After that, type the following formula:

=INDIRECT(ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4), ROW(B4)-ROW($B$4)+COLUMN($B$4)))

Press Enter.

Thus we have got the transposed data set, but again, we have to format the output data manually.

One of the advantages of this method is you can edit the transposed data without altering the whole data set.

4. Use Excel Power Query Tool to Transpose Multiple Rows to Columns

Power Query is a truly effective tool to transpose data quickly in Excel. This time, we will show the steps to transpose data using the Power Query tool.

Steps:

Firstly, select the data set and go to the Data tab > Get & Transform Data > From Table/Range.
The Create Table dialog box will open up > Mark the My table has headers checkbox > then press OK.

The Power Query Editor dialog box will open up > Select Transform tab.
Select Use Headers as First Row.

Then click on Transpose.

Then select Use First Row as Headers.

Go to File > Close & Load.

The Power Query Editor dialog box will be closed and consequently, a new sheet containing the transposed data will be loaded into the workbook.

5. Use of Excel VBA Macros to Convert Rows to Columns

The VBA macros also help convert rows to columns in the worksheet. In this section, we’ll show you how to work with VBA macros to transpose rows to columns in MS Excel.

Steps:

First of all, right-click on the Worksheet name “Use of VBA Macros” > Click on the View Code.

Microsoft Visual Basic Applications Module window will open up.

Copy the following VBA macros and just paste them into the Module window.

Option Explicit
Sub TransposeRowsToColumns()
Dim SrcRng As Range
Dim DestRng As Range
Set SrcRng = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRng = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
SrcRng.Copy
DestRng.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub

Then press F5 to run the VBA macros > Select the entire data set and press OK.

Select the location where you finally want to place the desired output.

Finally, we’ve converted the rows into columns using the VBA macros.

Conclusion

So dear readers, we have discussed five techniques to transpose rows to columns in Excel. Hope you will find this tutorial instrumental. Moreover, the workbook is there for you to download and practice yourself. However, if you have any questions, comments, or any kind of feedback, please don’t hesitate to let me know in the comment box.

How to Transpose Rows to Columns in Excel (5 Useful Methods)

Download Practice Workbook

5 Useful Methods to Transpose Rows to Columns in Excel

1. Use Paste Special Command and Link to Transpose Rows to Columns

1.1 Using Paste Special Command

1.2 Transpose a Table and Link It to Initial Data

2. Use TRANSPOSE Function to Invert Rows to Columns

2.1 Use TRANSPOSE Function as a Dynamic Array

2.2 Use TRANSPOSE Function with Ctrl+Shift+Enter

3. Use INDIRECT & ADDRESS Functions to Transpose Rows to Columns

4. Use Excel Power Query Tool to Transpose Multiple Rows to Columns

5. Use of Excel VBA Macros to Convert Rows to Columns

Conclusion

Related Articles

Masum Mahdy

How to Find Outliers in Regression Analysis in Excel (3 Easy Ways)

How to Group Tabs Under a Master Tab in Excel (with Quick Steps)

How to Create an Operating Budget in Excel (with Detailed Steps)

[Fixed:] Excel Spreadsheet Formulas Not Updating Automatically

Leave a reply Cancel reply