**Microsoft Excel **is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default **Excel Functions** that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. There are times when we need to exchange columns and rows. But doing that manually will take a considerable amount of time. That’s also tiresome. To avoid the hassle we can apply quite some methods. This article will show you **6** easy ways to **Swap Columns** and **Rows **in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## 6 Easy Methods to Swap Columns and Rows in Excel

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains **Salesman **and **Net Sales**. Here, we’ll show how you can switch columns and rows so that the **Salesman **names appear in a single row.

### 1. Swap Columns and Rows in Excel with Paste Transpose Feature

The easiest way is to use the **Paste Transpose **feature. We’ll paste the values by transposing. Therefore, follow the steps below to perform the task.

**STEPS:**

- First, copy the range
**B4:C8**by pressing the**Ctrl**and**C**keys. - Then, right-click on cell
**B10**. - Choose
**Paste Transpose**from the**Context Menu**.

- Thus, it’ll return the desired output as it’s shown below.

### 2. Use Find & Replace Feature for Swapping Columns and Rows

We can also use the **Find & Replace** feature. It finds a value and replaces it with another value. So, learn the following steps to carry out the operation.

**STEPS:**

- Firstly, go to
**File ➤ Options**. - As a result, the
**Excel Options**dialog box will pop out. - There, in the
**Formulas**tab, check the**R1C1 reference style**box. - Press
**OK**.

- Consequently, the column headers will be numbers.
- Now, in the
**R10C2**cell, type**AAR4C2**. - Next, input other cell values as shown in the following figure.
- Hence, you’ll see your dataset like the one displayed below.

- After that, press
**Ctrl**and**H**keys simultaneously to get the**Find and Replace**dialog box. - Under that
**Replace**tab, put**AA**in**Find what**and**=**in**Replace with**. - Lastly, press
**Replace****All**.

- In this way, you can swap columns and rows in excel.

### 3. Insert Excel TRANSPOSE Function to Exchange Rows and Columns

**The TRANSPOSE function** interchanges columns and rows. Hence, follow the process.

**STEPS:**

- First of all, click cell
**B10**. - Then, input the formula:

`=TRANSPOSE(B4:C8)`

- After pressing
**Enter**, it’ll spill the output.

### 4. Swap Columns and Rows by Merging INDIRECT and ADDRESS Functions

**The INDIRECT function** retrieves cell value if we specify the cell reference as a text string. **The ADDRESS function** returns the cell reference after we insert the column and row numbers. This method will combine these functions to switch rows and columns.

**STEPS:**

- Firstly, click cell
**B10**. - Insert the formula:

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

- Subsequently, hit
**Enter**and use**AutoFill**to get the other outputs.

🔎 **How Does the Formula Work?**

**ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4), ROW(B4)-ROW($B$4)+COLUMN($B$4))**

This part of the formula retrieves the location of the **B4 **cell and does the computation to swap the values.

**INDIRECT(ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4), ROW(B4)-ROW($B$4)+COLUMN($B$4)))**

Finally, the formula returns the cell value according to the cell reference.

### 5. Apply Excel Power Query to Switch Rows and Columns

However, the **Power Query Editor** feature can also swap columns and rows. It helps to import data from various sources. We can then transform or modify the data as per our requirements. So, the** Power Query** is another tool that can swap columns & rows.

**STEPS:**

- Select the range
**B4:C8**at first. - Then, go to
**Data ➤ Get & Transform Data ➤ From Table/Range**.

- A dialog box will pop out and press
**OK**. - Afterward, the
**Power Query Editor**window will emerge. - Go to the
**Transform**tab and choose**Use Headers as First Row**from the**Use First Row as Headers**drop-down.

- After that, select
**Transpose**.

- Again, select
**Use First Row as Headers**from the**Use First Row as Headers**drop-down. - Next, press
**Home ➤ Close & Load**.

- Thus, a new sheet will appear containing the swapped results.

### 6. Embed VBA Code for Swapping Columns and Rows in Excel

Moreover, the **VBA **code will be effective if we want to skip complex steps. So, learn the below process.

**STEPS:**

- In the beginning, go to
**Developer ➤ Visual Basic**.

- Now, select
**Insert ➤ Module**. - Next, copy the below code and paste it into the pop-out
**Module**box.

```
Sub Swap()
Dim rg As Range
Dim vr As Variant
Dim i1 As Integer, i2 As Integer, i3 As Integer
On Error Resume Next
xTitleId = "Swap"
Set rg = Application.Selection
Set rg = Application.InputBox("Range", xTitleId, rg.Address, Type:=8)
vr = rg.Formula
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i2 = 1 To UBound(vr, 2)
i3 = UBound(vr, 1)
For i1 = 1 To UBound(vr, 1) / 2
xTemp = vr(i1, i2)
vr(i1, i2) = vr(i3, i2)
vr(i3, i2) = xTemp
i3 = i3 - 1
Next
Next
rg.Formula = vr
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("B10:F11").Value = WorksheetFunction.Transpose(Range("B4:C8"))
End Sub
```

- Press
**F5**to run the code. - A dialog box will appear and select any cell as your destination.
- In this example, we select
**B10**. - So, the output will get spilled here.
- See the below picture for a better understanding.

## How to Swap Two Columns in Excel

Similarly, you may want to swap two columns. You can’t do that if you have merged cells. Follow the steps below.

**STEPS:**

- Firstly, select the entire column by clicking on the column header.
- Move your cursor till you get a shape like an all-around arrow key as shown below.

- After that, drag the cursor to the left before the
**Salesman column.** - Then, press the
**Shift**key and drop the column. - Hence, the columns will get swapped.
- Look at the picture below.

## How to Swap Columns and Rows in Excel Chart

Moreover, you can also swap columns and rows in charts. Therefore, follow the steps below to carry out the operation.

**STEPS:**

- First, we insert a
**2-D stacked column**chart after choosing the required range.

- Next, click on the chart.
- Subsequently, select
**Switch Row/Column**under**Chart Design**.

- Thus, the columns will be rows and the rows will be columns in the chart.
- See the chart below where we apply the swapping.

## Conclusion

Henceforth, you will be able to **Swap Columns **and **Rows **in **Excel** following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.