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.
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.
- 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.
- 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.
- First of all, click cell B10.
- Then, input the formula:
- 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.
- Firstly, click cell B10.
- Insert the formula:
- 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.
- 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.
- 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.
- 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.
- 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.
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.