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.
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.
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.
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.
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.
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.
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.
Read More: How to Transpose Array in Excel (3 Simple Ways)
Similar Readings
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Convert Columns to Rows in Excel Using Power Query
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
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.
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.
- 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.
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.
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.
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
- How to Transpose Columns to Rows In Excel (6 Methods)
- How to Convert Columns to Rows in Excel Based On Cell Value
- VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)
- How to Convert Column to Comma Separated List With Single Quotes
- Data clean-up techniques in Excel: Changing vertical data to horizontal data
- How to Swap Rows in Excel (2 Methods)
- Convert Columns to Rows in Excel (2 Methods)
- Excel Transpose Formulas Without Changing References (4 Easy Ways)