Everyone has their own point of view when visualizing and representing data. Some may feel comfortable working with data when they are in the horizontal direction, while others may prefer to work with them when they are vertically represented. So sometimes we need to change the overview of a whole large worksheet based on the user’s need. In Excel, you can do that quickly and efficiently. This article will show you how to transpose columns to rows in Excel with the 8 most effective and easy ways.
8 Most Effective Methods to Transpose Columns to Rows in Excel
In the following article, we will describe the 8 most effective and easiest methods to transpose columns to rows in Excel. Here, we used Excel 365. You can use any available Excel version.
1. Using Paste Special Feature
The copy-and-paste method is the most frequently used method in Excel due to its simplicity to organize computerized documents. It is widely being used from simple modification to extensive modification in all kinds of digital tasks around the world. In this method, we will use the Copy-and-Paste method to transpose a single column to a single row.
1.1. Transposing a Single Column to a Single Row
Here, we will use the Copy and Paste method to transpose a single column to a single row.
Steps:
- In the beginning, we will select cells B4:B10 >> press CTRL+C.
- This will copy these cells.
- Here, you can right-click on the selected cells and select Copy from the Context Menu.
- After that, select the cell that you want to have the first value of the row. Here, we selected cell B13.
- Furthermore, right-click the mouse >> in the Paste Options section >> select Transpose (T), shown in the following picture.
- Afterward, once you select the Transpose (T) Paste Option, it will transform the column into a row.
- Therefore, you can see the transposed row in the following picture.
Read More: How to Convert Single Columns to Rows in Excel with Formulas
1.2. Transposing Multiple Columns to Multiple Rows
Here, we will use the Copy and Paste method to transpose multiple columns to multiple rows.
Steps:
- In the beginning, we will select cells B4:C10 >> press CTRL+C.
- This will copy these cells.
- Here, you can right-click on the selected cells and select Copy from the Context Menu.
- After that, select the cell that you want to have the first value of the row. Here, we selected cell B13.
- Furthermore, right-click the mouse >> in the Paste Options section >> select Transpose (T), shown in the following picture.
- Afterward, once you select the Transpose (T) Paste Option, it will transform the column into a row.
- Hence, you can see the transposed rows in the following picture.
Read More: How to Convert Multiple Columns into a Single Row in Excel
2. Using TRANSPOSE Function
In this method, we will use the TRANSPOSE function to transpose columns to rows in Excel.
2.1. TRANSPOSE Function for Converting a Single Column to a Single Row
Here, we will use the TRANSPOSE function for a single column.
Steps:
- In the first place, we will type the following formula in cell B13.
=TRANSPOSE(B4:B10)
Formula Breakdown
- TRANSPOSE(B4:B10) → the TRANSPOSE function converts the column to a row.
- B4:B10 → is the array.
- After that, press ENTER.
- Therefore, you will see the transposed row.
Read More: How to Convert Columns to Rows in Excel
2.2. TRANSPOSE Function for Conversion of Multiple Columns to Multiple Rows
Here, we will use the TRANSPOSE function to transpose multiple columns to rows in Excel.
Steps:
- In the beginning, we will type the following formula in cell B13.
=TRANSPOSE(B4:C8)
- Afterward, press ENTER.
- As a result, you will see the transposed rows.
Read More: Transpose Multiple Rows in Group to Columns in Excel
3. Combining OFFSET, COLUMNS, and ROWS Functions
In this method, we will use the combination of OFFSET, COLUMNS, and ROWS functions to transpose a single column to multiple rows in Excel.
Steps:
- In the beginning, we will type the following formula in cell C17.
=OFFSET($B$5, COLUMNS($B: B)-1+(ROWS($5:5)-1)*5,0)
Formula Breakdown
- The OFFSET function gives out a reference to a range which is a particular number of rows and columns from a cell or a number of cells.
- The COLUMNS function gives out the number of columns in a range.
- The ROWS function finds the number of rows in a range.
- OFFSET($B$5,COLUMNS($B:B)-1+(ROWS($5:5)-1)*5,0) → becomes
- Output: 1
- At this point, press ENTER.
- Therefore, you can see the result in cell C17.
- Furthermore, we will drag the formula horizontally up to cell G17 using the Fill Handle tool.
- As a result, you can see one transposed row.
- Next, we will drag down the formula of cell C17 to cell C18 using the Fill Handle tool.
- This will give us another transposed row.
- Therefore, you can see a value in cell C18.
- Moreover, we will drag the formula horizontally up to cell G18.
- Hence, you can see two transposed rows.
Read More: How to Transpose Formulas Without Changing References in Excel
4. Applying Cell Reference to Transpose Columns to Rows in Excel
Transposing columns to rows using cell referencing is a very efficient way while work with a large amount of data.
The steps of implementing cell referencing to transpose columns to rows are described below.
Steps:
- First of all, we will simply type saB5 in cell C13.
- Here, we include sa with the cell reference, you can type anything according to your choice.
- In addition, we will drag the value horizontally up to cell H13 with a Fill Handle tool.
- Therefore, you can see the Odd Number row.
- Next, we will type saC5 in cell C14.
- In addition, we will drag the formula horizontally with a Fill Handle tool.
- Therefore, you can see two rows.
- Next, we will replace sa with “=” so that the cells extract the value from the columns.
- To do so, we will go to the Home tab >> go to the Editing group.
- Then, from Find & Select >> select Replace.
- At this point, a Find and Replace dialog box will appear.
- Afterward, in the Find what box, type sa.
- Moreover, in the Replace with box, type =.
- Along with that, click on Replace All.
- At this point, a confirmation dialog box will pop up.
- Click OK.
- After that, close the Find and Replace dialog box.
- Therefore, you can see that rows now have the values of the columns.
- Hence, you can transpose columns to rows.
Read More: How to Transpose Rows to Columns Based on Criteria in Excel
5. Merging INDIRECT and COLUMN Functions to Transpose Columns to Rows in Excel
In this method, we will use the combination of INDIRECT and COLUMN functions to transpose columns to rows in Excel.
Steps:
- In the first place, we will type the following formula in cell F4.
=INDIRECT("b"&COLUMN()-1)
Formula Breakdown
- The INDIRECT function finds the reference to a particular text string.
- The COLUMN function finds the column number of a particular cell reference.
- INDIRECT(“b”&COLUMN()-1) →becomes
- Output: 1
- At this moment, press ENTER.
- Therefore, you can see the result in cell F4.
- Furthermore, we will drag the formula horizontally up to cell I4 with the Fill Handle tool.
- Therefore, you can see the complete Odd Number row.
- After that, we will type the following formula in cell F5.
=INDIRECT("c"&COLUMN()-1)
- At this moment, press ENTER.
- Therefore, you can see the result in cell F5.
- Furthermore, we will drag the formula horizontally up to cell I5 with the Fill Handle tool.
- As a result, you can see the transposed rows.
Read More: How to Reverse Transpose in Excel
6. Combining INDEX and COLUMN Functions to Transpose Columns to Rows in Excel
In this method, we will merge the INDEX and COLUMN functions to transpose columns to rows in Excel.
Steps:
- In the beginning, we will type the following formula in cell F4.
=INDEX($B$4:$C$8, COLUMN()-4,1) 
Formula Breakdown
- The INDEX function finds a value with a table or range.
- The COLUMN function finds the column number of a particular cell reference.
- INDEX($B$4:$C$8,COLUMN()-4,1) → becomes
- Output:1
- At this moment, press ENTER.
- Therefore, you can see the result in cell F4.
- Furthermore, we will drag the formula horizontally up to cell I4 with the Fill Handle tool.
- Therefore, you can see the complete Odd Number row.
- Afterward, we will type the following formula in cell F5.
=INDEX($B$4:$C$8,COLUMN()-4,1)
- At this point, press ENTER.
- As a result, you can see the result in cell F5.
- Moreover, we will drag the formula horizontally up to cell I5 with the Fill Handle tool.
- Hence, you can see two transposed rows.
Read More: How to Reverse Order of Columns Horizontally in Excel
7. Use of Power Query to Transpose Columns to Rows in Excel
In this method, we will use the Power Query to transpose columns to rows in Excel.
Steps:
- In the first place, we will select the entire dataset by selecting cells B4:C11.
- After that go to the Data tab >> select From Table/Range.
- At this moment, a Create Table dialog box will appear.
- Here, make sure My table has headers marked.
- Then, click OK.
- Therefore, you can see the created Power Query of the dataset.
- Then, we will go to the Transform tab >> select Transpose.
- Therefore, you can see the transposed rows in the Power Query.
- Now, it is time to shift the transposed dataset to our Worksheet.
- To do so, we will go to the Home tab.
- Then, from the Close & Load group >> select Close & Load To.
- At this point, an Import Data dialog box will pop up.
- Here, we want to import data in the existing worksheet, therefore, we select Existing Worksheet.
- Moreover, we select cell B14 for the location.
- Then, click OK.
- Hence, you can see the transposed rows in the existing worksheet.
Read More: Convert Columns to Rows in Excel Using Power Query
8. Inserting VBA Macro to Transpose Columns to Rows in Excel
This is a quick and handy procedure to transpose columns to rows in Excel.
Steps:
- First, go to the Developer tab >> select Visual Basic.
- This will bring out a VBA Editor window.
- You can also press ALT+F11 to bring out the VBA Editor window.
At this point, a VBA Editor window will appear.
Then, from the Insert tab >> select Module.
- Furthermore, we will type the following code in the Module.
Sub Transpose_Columns_to_Rows()
Dim work_range As Range
Dim target_range As Range
Set work_range = Application.InputBox _
(Prompt:="Select the columns", Type:=8)
Set target_range = Application.InputBox _
(Prompt:="Select location cell", Type:=8)
work_range.Copy
target_range.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub
- Then, Save the Code.
- Now, return to the Worksheet, go to the Developer tab >> select Macro.
- Next, select the Sub-procedure of the code >> click on Run.
- Therefore, an Input window will appear.
- Then, we will select columns B4:B9 in the Select the column box >> click OK.
- In addition, another Input window will appear.
- Here, we will select cell B12 in the Select the location box >> click OK.
- Therefore, you can see a transposed row in cell B12:G12.
- Furthermore, we again run the code for the Even Number column.
- Therefore, you can see two transposed rows.
Read More: VBA to Transpose Multiple Columns into Rows in Excel
Things to Remember
- While using the Transpose function, if you are not using Excel 365, then don’t hit only ENTER, hit CTRL + SHIFT+ ENTER.
- Remember the Transpose function is not working with just a single data, it is working with a large amount of data so don’t forget to select a range of cells while transposing your dataset.
Do It Yourself
You can download the above Excel file and practice the explained methods.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Here, we show you 8 easy methods to transpose columns to rows in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.
Related Articles
- How to Swap Rows in Excel
- Excel VBA to Transpose Array
- How to Paste Transpose in Excel Using Shortcut
- Conditional Transpose in Excel
- How to Transpose Rows to Columns Using Excel VBA
- How to Convert Column to Comma Separated List With Single Quotes
