5 Ways to Transpose Data in Excel (Step-by-Step)

Transposing data in Excel is a useful technique to switch rows and columns. Here are five methods to transpose data effortlessly:

Method 1: Using Paste Special

1. Copy the range of data you want to transpose.
2. Right-click on the cell where you want to paste the transposed data.
3. Choose Paste Special.
4. In the Paste Special dialog box, check the Transpose option.
5. Click OK to transpose the data.

Method 2: Using Transpose Function

1. Select a new range of cells where you want the transposed data to appear.
2. Enter the TRANSPOSE function in the formula bar: =TRANSPOSE(original_range).
3. Press Ctrl + Shift + Enter to enter the formula as an array formula.

Method 3: Using Power Query

1. Select your data range.
2. Go to the Data tab on the ribbon.
3. Click on From Table/Range to open Power Query Editor.
4. In Power Query, select the columns you want to transpose.
5. Go to the Transform tab on the ribbon.
6. Click on Transpose.

Method 4: Using INDEX and SMALL Functions

1. In a new range, enter =INDEX(original_range, COLUMN(), ROW()).
2. Press Ctrl + Shift + Enter to enter the formula as an array formula.

Method 5: Using VBA Macro

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module to insert a new module.
3. Copy and paste the following VBA code:

Sub TransposeData()
    Dim rng As Range
    Set rng = Application.InputBox("Select the range to transpose:", Type:=8)
    rng.Copy
    rng.Offset(1, rng.Columns.Count).PasteSpecial Transpose:=True
    Application.CutCopyMode = False
End Sub

4. Press F5 to run the macro and follow the on-screen instructions.