Transpose Data in Excel (Applying Functions, Commands, and VBA)

Get FREE Advanced Excel Exercises with Solutions!

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

An Overview image of Transpose in Excel


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.

  1. Using Paste Special tool.
  2. Applying TRANSPOSE function.
  3. Combining INDEX and COLUMN function
  4. Merging XLOOKUP and TRANSPOSE functions
  5. 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.

Getting the Paste Special tool

  • Thus a Paste Special dialog box appears. Now select the transpose option >> then hit the OK button.

Transposing data from the Paste Special dialog.

  • Therefore, we obtain transposed data of the B4:E8 range in the B10:F13 range.

Obtaining the transposed data in Excel


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.

Opening Paste Special dialog and transposing data.

  • Consequently, we get the transposed result in the B11:F14 range.

Getting the transposed data in Excel


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)

Using the Excel TRANSPOSE function


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.

Using OFFSET function

  • Therefore, you will achieve the transposed data in the B11:F14 range.

Achieving the transposed data in Excel


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.
Using INDIRECT and COLUMN functions

Click to get detailed view of the image


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.
Using INDEX and COLUMN functions

Click to get detailed view of the image


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.

Using XLOOKUP and TRANSPOSE functions to get particular data in Excel


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.

Inserting strings

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

Using the Find and Replace tool

  • Therefore, we obtain the transposed result in the B10:F13 range.

Obtaining the transposed data


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.

Getting the Power Query tool

  • Thus a Power Query window appears. Then click as follows, Transform tab >> Use Headers as First Row option >> Transpose command.

Using the Transpose command

  • After that select the Use First Row as Headers command.

Using the first row as headers

  • Further, go to the File menu >> select Close and Load To option.

Exporting and closing power query

  • Therefore, an Import Data dialog appears. Select the Existing Worksheet and B10 cell in the PowerQuery worksheet >> Then hit the OK button.

Importing data to the existing worksheet.

  • Finally, we get the transposed data in the B10:F13 range.

Obtaining the transposed data via Excel Power Query tool.


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.

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

Output of running Excel VBA code to transpose data


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


<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo