How to Swap Columns and Rows in Excel (6 Easy Methods)

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.


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.

how to swap columns and rows in excel


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 keys.
  • Then, right-click on cell B10.
  • Choose Paste Transpose from the Context Menu.

Swap Columns and Rows in Excel with Paste Transpose Feature

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

Use Find & Replace Feature for Swapping Columns and Rows

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

Insert Excel TRANSPOSE Function to Exchange Rows and Columns


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.

Swap Columns and Rows by Merging INDIRECT and ADDRESS Functions

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

Apply Excel Power Query to Switch Rows and Columns

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

Embed VBA Code for Swapping Columns and Rows in Excel

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

How to Swap Two Columns in Excel

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

How to Swap Columns and Rows in Excel Chart

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

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo