How to Swap Columns and Rows in Excel

This is an overview.

how to swap columns and rows in excel

This dataset includes Employee Information: ID, Full Name, Designation, Department, and Annual Salary.

dataset of employee information of an organization

Method 1 – Using the Paste Special Feature to Swap Columns and Rows

Select the entire dataset (B4:G8) and press CTRL + C to copy the range.

copying the dataset

Note: For larger datasets, click any cell and press CTRL + A to select the whole dataset.

Right-click the destination cell (B10) and select Paste Special.

clicking on Paste Special option

In the Paste Special dialog box, check Transpose and click OK.

checking the Transpose option

This is the output.

columns and rows swapped in Excel

Follow the same steps to reverse the result:

again transposed to previous form

Method 2 – Utilizing the Find & Replace Feature to Make Swapping Dynamic

Select the entire dataset and copy it.
Open the Paste Special dialog box following the steps in Method 1.
Click Paste Link.

clicking on Paste Link option in the Paste Special dialog box in Excel

It’ll paste the data without formatting in the selected output range.

columns and rows swapped without formatting

Select the pasted range (B10:G14) and go to the Home tab.
Select Find & Replace >> Replace. You can also press CTRL + H.

opening Find & Replace feature

In the Find and Replace dialog box, enter = and xx in Find what and Replace with.
Click Replace All.

replacing equal sign with xx in selected range in Excel

The message “All done. We made 30 replacements.” is displayed. xx replaced “=”.

Click OK.

equal signs in formulas got replaced with xx

Copy B10:G14 and paste it in B16.
Right-click.
Select Transpose (T) in Paste Options.

copying data and pasting using Transpose as Paste options in excel

This is the output.

pasted as transposed data in Excel

Select the pasted range and press CTRL + H to open the Find and Replace dialog box.

opening find & replace dialog box again

Enter xx and = in Find what and Replace with.
Click Replace All.

again replacing all equal signs with xx

This is the output.

swap columns and rows using Find & Replace feature

This is the output (after formatting and hiding rows).

final look after applying formatting

Method 3 – Swapping Columns and Rows in Large Datasets Using the Power Query Editor

Select the dataset and go to Data >> From Table/Range.

getting data from table or range

In the Create Table dialog box, the selected table range is displayed.

Click OK.

working on create table dialog box

In the Power Query Editor window, select Transform >> Transpose in Table.

transpoing in power query editor to swap columns and rows in Excel

The table is transposed.

Select Use First Row as Headers.

clicking on use first row as headers to eliminate the default columns headers in power query editor

In the Home tab, click Close & Load >> Close & Load To… .

In the Import Data dialog box, choose the output destination. Here, Existing worksheet and B11 as cell reference.
Click OK.

defining output range of the data

This is the output.

swap columns and rows in the existing worksheet using power query feature in Excel

Method 4 – Using the TRANSPOSE Function to Update Data Automatically

In B10, use the following formula and press ENTER.

=TRANSPOSE(B4:G8)

As it’s an array formula, it returns an array containing the transposed data without formatting.

using TRANSPOSE function to swap columns and rows in Excel

Note: Press CTRL + SHIFT + ENTER if you are using versions other than Excel 365.

This is the output (after formatting).

columns and rows swapped and added formatting in Excel

Note: This method is also dynamic.

Method 5 – Swapping Columns and Rows using the OFFSET Function

Enter serial numbers from 0 to the (total row number-1) or (5 – 1) or 4 (there are 5 rows) in an area to paste the data; enter serial numbers from 0 to the (total column number-1) or (6 – 1) or 5 (there are 6 columns) in the left of the that area.

writing serial number to get help in the process

Use the following formula in B11 and press ENTER.

=OFFSET($B$4,B$10,$A11)

Drag down the Fill Handle to copy the formula to the other cells.

using OFFSET function to swap columns and rows in Excel

Apply formatting.

added formatting to look like the original data

Select B10. The formula bar shows 0, but it isn’t visible in the worksheet cell.

You can hide extra numbers around the output cells by changing their font color to white.

serial numbers got hidden

Method 6 – Use the VLOOKUP Function

Transpose the first column and first row manually and enter the values in the first row and first column of the output range.

transposed data manually to start the process

In C11, use the following formula and press ENTER.

=VLOOKUP($B11,TRANSPOSE($C$4:$G$8),COLUMN()-1,FALSE)

using VLOOKUP with TRANSPOSE and COLUMN functions to swap columns and rows in Excel

This is the output.

Method 7 – Swap Columns and Rows Using the INDEX Function

Enter the following formula in B10 and press ENTER.

using INDEX function with COLUMN and ROW functions to swap columns and rows in excel

=INDEX($B$4:$G$8,COLUMN(B2),ROW(B2))

This is the output (after formatting).

added formatting in column headers

Method 8 – Combine the INDIRECT and the ADDRESS Functions to Swap Columns and Rows

Use the following formula in B10.
Drag down the Fill Handle to see the result in the rest of the cells.

=INDIRECT(ADDRESS(COLUMN(C5)-COLUMN($B$4)+ROW($B$4), ROW(C5)-ROW($B$4)+COLUMN($B$4)))

using INDIRECT, ADDRESS functions to swap column and rows in Excel

Formula Breakdown

COLUMN(C5): returns the column number of C5.
COLUMN($B$4): returns the column number of B4. The dollar signs around the row and column reference indicate an absolute reference (the reference will not change if the formula is copied to other cells).
COLUMN(C5)-COLUMN($B$4): calculates the number of columns between the reference cell (B4) and the target cell (C5).
ROW($B$4): returns the row number B4.
ROW(C5)-ROW($B$4): calculates the number of rows between the reference cell (B4) and the target cell (C5).
COLUMN(C5)-COLUMN($B$4)+ROW($B$4): adds the number of columns and rows calculated in steps 3 and 4 to the row number in the reference cell.
ROW(C5)-ROW($B$4)+COLUMN($B$4): adds the number of rows and columns calculated in step 5 to the column number in the reference cell.
ADDRESS(COLUMN(C5)-COLUMN($B$4)+ROW($B$4), ROW(C5)-ROW($B$4)+COLUMN($B$4)): takes the row and column values calculated in steps 6 and 7 and returns the cell address as a text string.
INDIRECT(ADDRESS(COLUMN(C5)-COLUMN($B$4)+ROW($B$4), ROW(C5)-ROW($B$4)+COLUMN($B$4))): converts the text string returned by the ADDRESS function into a reference to the target cell. The INDIRECT function references a cell based on a text string.

Method 9 – Applying a VBA Code to Swap Columns and Rows in Large Datasets in Excel

Go to the Developer tab and click Visual Basic in Code.

navigating to Developer tab

In the Microsoft Visual Basic for Applications window:

Select Insert and choose Module.
Enter the VBA code.

inserting new code module

Sub Swap_Columns_Rows()

Dim inputRange As Range
Dim outputCell As Range
Dim outputRange As Range

'Get input range
Set inputRange = Application.InputBox("Select the range to transpose:", Type:=8)

'Get output cell
Set outputCell = Application.InputBox("Select the first cell of the output range:", Type:=8)

'Transpose data and copy formatting
Set outputRange = outputCell.Resize(inputRange.Columns.Count, inputRange.Rows.Count)
inputRange.Copy
outputRange.PasteSpecial Transpose:=True
inputRange.Copy
outputRange.PasteSpecial Paste:=xlPasteFormats, Transpose:=True

End Sub

VBA code to swap columns and rows in Excel