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.

Transpose rows to Columns in Excel


Download Practice Workbook

You can download the practice book from the link below.


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.

Transpose Rows to Columns in Excel


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.

Transpose Rows to Columns with Paste Special Command

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

Transpose Rows to Columns with Paste Special Command

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

Paste Special Dialog Box

  • Press OK.

Transpose Rows to Columns in Excel: Output

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

Read More: Transpose Multiple Rows in Group to Columns in Excel.


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.

Transpose a Table and Link It to the Initial Data

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.

Transpose Rows to Columns in Excel

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

Find and Replace Dialog Box

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.

Read More: Excel Paste Transpose Shortcut: 4 Easy Ways to Use


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.

Transpose Rows to Columns in Excel

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

Transpose Rows to Columns in Excel

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.

Transpose Rows to Columns in Excel

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

Transpose Rows to Columns in Excel

Read More: How to Transpose Array in Excel (3 Simple Ways)


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)))
Transpose Rows to Columns in Excel
  • Press Enter.

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

Transpose Rows to Columns in Excel

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

Read More: Transpose Multiple Rows in Group to Columns in Excel


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.

Transpose Rows to Columns in Excel

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

Transpose Rows to Columns in Excel

  • Then click on Transpose.

Transpose Rows to Columns in Excel

  • Then select Use First Row as Headers.

Transpose Rows to Columns in Excel

  • Go to File > Close & Load.

Transpose Rows to Columns in Excel

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

Transpose Rows to Columns in Excel

Read More: Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)


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.

Transpose Rows to Columns Using VBA Macros

Microsoft Visual Basic Applications Module window will open up.

Transpose Rows to Columns in Excel

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

Transpose Rows to Columns in Excel

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

Transpose Rows to Columns in Excel

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

Transpose Rows to Columns in Excel

Read More: Excel VBA: Transpose Multiple Rows in Group to Columns


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.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo