In this Excel tutorial, you will learn to transpose in Excel by applying 10 useful approaches addressing numerous tools such as Paste Special, Find and Replace, Power Query, and VBA Macro as well as functions like TRANSPOSE, OFFSET, INDEX, INDIRECT, XLOOKUP, and so on..
We have used Microsoft Office 365 while generating the content. Although the other functions are available to the older Excel versions from MS Excel 2007, the XLOOKUP function is available in Excel 2021 and the newer versions.
Nearly every Excel user switches between rows and columns. Let’s say, you like to gather Sales data based on Sales Region instead of Months. However, there is no alternative without transforming the dataset. In Excel, the transformation of horizontal data into vertical and vertical data into horizontal is known as “Transpose”.
Download Practice Workbook
How Many Ways to Transpose in Excel?
There are 10 different approaches to converting columns to rows in Excel by applying different Excel tools and functions. They are as follows.
- Using Paste Special tool.
- Applying TRANSPOSE function.
- Combining INDEX and COLUMN function
- Merging XLOOKUP and TRANSPOSE functions
- Using Power Query tools, and so on.
1. Using Excel Paste Special Tool to Transpose Data
Yes, you can transpose a set of data using the Transpose command from the Paste Special tool. Also, it is the easy, basic, and shortest approach of converting rows to columns.
- Initially, type Ctrl + C to copy the B4:E8 range.
- Then, select B10 to transpose >> go to the Home tab >> Expand the Paste command >> select the Paste Special tool.
- Thus a Paste Special dialog box appears. Now select the transpose option >> then hit the OK button.
- Therefore, we obtain transposed data of the B4:E8 range in the B10:F13 range.
2. Applying Keyboard Shortcut for Transposing Data
The keyboard shortcut is Ctrl+ Alt + V buttons to open the Paste Special dialog box and transpose the data accordingly.
- Initially, copy the range B4:E8 by typing Ctrl + C keys.
- Next, select B11 cell (transpose location) >> type Ctrl+ Alt + V keys to open the Paste Special dialog.
- Further, select Transpose from the Paste Special dialog>> hit the OK button.
- Consequently, we get the transposed result in the B11:F14 range.
3. Inserting TRANSPOSE Function Switch Rows to Columns
You can use the dedicated function named the TRANSPOSE function to transpose a range/table in Excel. It uses an array as an argument and returns a range. It is safe to say the TRANSPOSE function replicates the use of the Transpose command in Excel.
- Therefore, we obtain the transposed result in the B10 cell using the following formula.
=TRANSPOSE(B4:E8)
4. Applying Excel OFFSET Function to Transpose Data
Yes, the OFFSET function is applicable to transpose data, it works based on reference argument. To apply the OFFSET function, we must set the rows and column numbers to transpose a dataset.
- First Write B10=0, C10=1, D10=2, E10=3, and F10=4 as the number of rows.
- Similarly, type A11=0, A12=1, A13=2, and A14=3 as the number of columns.
- Then, input the following formula in the B11 cell containing the OFFSET function.
=OFFSET($B$4,B$10,$A11)
- Next, use the Fill Handle to autofill the data horizontally and vertically.
- Therefore, you will achieve the transposed data in the B11:F14 range.
5. Combining INDIRECT and COLUMN Functions
Yes, the combination of INDIRECT and COLUMN functions can transpose a dataset. The INDIRECT function Store data from the reference specified by a text string whereas the COLUMN function shows the number of columns.
- Insert the formulas below containing the INDIRECT function with the COLUMN function in the B11, B12, B13, and B14 cells.
=INDIRECT("B"&COLUMN()+2)
=INDIRECT("C"&COLUMN()+2)
=INDIRECT("D"&COLUMN()+2)
=INDIRECT("E"&COLUMN()+2)
- From the formula, COLUMN()= 2 that dictates (“B”&COLUMN()+2) = B4.
- Therefore, the INDIRECT function picks the data of the B4 Similarly call the data of C4, D4, and E4.
- In the end, we obtain transposed data in the B11:F14 range using the Fill Handle tool.
6. Merging Excel INDEX and COLUMN Functions
We can combine INDEX and COLUMN functions to transpose a dataset. The INDEX function returns a certain reference or value of the cell of a particular row and column whereas the COLUMN function picks up the column number.
- Use the following formulas in the B10, B11, B12, and B13 cells respectively.
=INDEX($B$4:$E$8,COLUMN()-1,1)
=INDEX($B$4:$E$8,COLUMN()-1,2)
=INDEX($B$4:$E$8,COLUMN()-1,3)
=INDEX($B$4:$E$8,COLUMN()-1,4)
- From the formula of the B10 cell, COLUMN()=2, and COLUMN()-1=2-1=1 is the row number.
- Therefore, We get Sale’2023 in the B10 Similarly get the data in the B11, B12, and B13 cells.
- After that, we get the transposed result in the B10:F13 range using the Fill Handle tool.
7. Joining TRANSPOSE and XLOOKUP Functions
By merging the XLOOKUP function with the TRANSPOSE function we can simply transpose a specific row or column. The XLOOKUP function is a wise choice if you are looking for a specific value.
- Inserting the following formula in the B12 cell, we obtain transposed results for Texas.
=TRANSPOSE((XLOOKUP(C10,B4:E4,B4:E8,"No Result",2)))
- From the formula, the XLOOKUP function finds the C10 = Texas in the B4:E4 Then, gather the data from the B4:E8 range. It will type No Result if it is unable to find a result. 2 for the wildcard character match.
- Finally, the Excel TRANSPOSE function transposes the search result.
8. Using Find and Replace Feature to Shift Rows to Columns
There is a useful approach to transposing a dataset using the Find and Replace tool. You may consider this approach as a trick. From smaller to larger, everywhere it is applicable. Please follow the below steps attentively.
- First, Write TB4, TC4, TD4, and TE4 in the B10, B11, B12 and B13 cells respectively.
- Then use the Fill Handle tool to autofill the B10:F13 range.
- Next after getting the data in the B10:F13 cell, type Ctrl + H to get the Find and Replace tool.
- After that insert T in the Find what field and = in the Replace with field from the Replace menu.
- Further, click on the Replace All button.
- Therefore, we obtain the transposed result in the B10:F13 range.
9. Using Power Query Tool to Transpose Columns to Rows
Yes, using the Power Query tool, you can transpose the dataset within a minute since it has a Transpose command to convert rows to columns and export to an Excel worksheet. You can consider this process as a duplicate application of the Paste Special feature.
- First, select the dataset >> go to the Data tab >> click on the From Table/Range command.
- Thus a Power Query window appears. Then click as follows, Transform tab >> Use Headers as First Row option >> Transpose command.
- After that select the Use First Row as Headers command.
- Further, go to the File menu >> select Close and Load To option.
- Therefore, an Import Data dialog appears. Select the Existing Worksheet and B10 cell in the PowerQuery worksheet >> Then hit the OK button.
- Finally, we get the transposed data in the B10:F13 range.
10. Applying VBA Macro Transpose Multiple Rows to Columns
You will be happy to know that VBA has a Transpose property that converts rows to columns with a simple code. Creating a sub-procedure and using it as a macro is a one-click solution. Here, you will find a sub-procedure that transposes the selected range and stores the transposed data one row after. You can modify the code based on your requirements.
- Write the codes in the Module and run by clicking the play icon or pressing the F5 key.
Sub TransposeData()
Dim Rng As Range
Dim lastRow, firstCol As Long
Set Rng = Selection
Rng.Copy
lastRow = Selection.Rows(Selection.Rows.Count).Row
firstCol = Selection.Columns(1).Column
Cells(lastRow + 2, firstCol).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub
What Are Things to Remember for Transposing in Excel?
- Paste Special tool to transpose a certain dataset. Besides, the Keyboard shortcut to open the Paste Special dialog.
- TRANSPOSE function to transpose data automatically. In addition, the OFFSET, INDIRECT, and INDEX functions call the reference and replicate the application of the TRANSPOSE function.
- XLOOKUP function joining the TRANSPOSE function transposes a particular column.
- Find and Replace tool to transpose eliminating strings.
- Power Query tool has a Transpose command that converts rows to columns automatically.
- VBA Macro to transpose employing the Paste Special properties.
Frequently Asked Questions(FAQs)
Q1. Does Excel VBA have transpose function?
No, VBA has no dedicated function named TRANSPOSE. However, there is a Transpose property under the VBA Paste Special feature that can convert rows to columns.
Q2. Where is the TRANSPOSE function in Excel?
Excel also has a built-in function in the Paste Special dialog. Go to the Home tab >> Expand the Paste command >> Select the Paste Special tool >> Select Transpose from the Paste Special dialog.
Q3. Can VBA update transpose values when data changes?
Yes, by writing VBA code in a worksheet module with a Change event, you can update automatically when data changes.
Conclusion
Therefore, I hope the provided approaches and guidelines throughout the content will be beneficial to you. The Transpose command is located in the Paste Special tool whereas TRANSPOSE is the dedicated Excel function to convert rows to columns. On the other hand, you can consider the Find and Replace tool as a trick to apply transpose. Besides, there are functions like OFFSET, INDEX, and INDIRECT that replicate the application of the TRANSPOSE function in Excel. Additionally, the Power Query tool has the Transpose command as VBA Macro has property. Further, XLOOKUP along with the TRANSPOSE function can transpose a specific column. Don’t forget to leave your insights and queries in the comment section.
Transpose in Excel: Knowledge Hub
- Convert Multiple Rows to Columns in Excel
- Transpose Multiple Columns to Rows
- How to Paste Link and Transpose in Excel
- How to Move Data from Row to Column in Excel
- How to Flip Data from Horizontal to Vertical
- How to Change Vertical Column to Horizontal in Excel
- How to Flip Columns and Rows in Excel
- Convert Multiple Rows to Columns in Macro
- Excel VBA Transpose Multiple Columns into Rows
- VBA Transpose Array
<< Go Back to Learn Excel