How to Swap Columns and Rows in Excel (9 Easy Ways)

Are you struggling with analyzing or viewing a complex Excel dataset? If so, swapping the columns and rows in your Excel spreadsheet might be the solution you are looking for. In this reference, we will discuss how to swap columns and rows in Excel using multiple techniques.
Reversing the data in this manner can make it easier to read, filter, sort, and analyze. Whether you are new to Excel or have been using it for some time, swapping the columns and rows can be accomplished using these various methods. So, let’s dive in and learn how to swap data in Excel!

Why Swapping Columns and Rows in Excel?

Let’s assume, we have Employee Information for a certain organization. This dataset includes the Employee ID, and their corresponding Full Name, Designation, Department, and Annual Salary in rows 4 to 8 respectively.

So, you can notice that our dataset is horizontally spread. In this way, this doesn’t look good, and it’s difficult to understand and get insight from here.
When a dataset is too long in the horizontal direction, it can become difficult to view on smaller screens or mobile devices.
If your data has row headers instead of column headers like the above dataset, filtering and sorting the data become challenging also.
Overall, swapping columns and rows in Excel is a useful concept for reorganizing and analyzing data, improving its visual presentation, and making it easier to apply filters and sorting.

How to Swap Columns and Rows in Excel: 9 Advanced Techniques

Now, we’ll utilize the above dataset to swap columns and rows in Excel using multiple methods. So, let’s explore them one by one.

Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Use Paste Special Feature to Swap Columns and Rows

The most accessible way is to use the Paste Special feature. We’ll paste the values by transposing. It’s user-friendly because of its simplicity. You don’t have to use any function or complex steps to do this.

• First, select the entire dataset (e.g. B4:G8) and press CTRL + C on your keyboard to copy the range.

• Then, right-click on the destination cell (cell B10) and select the Paste Special option from the context menu.

• In the Paste Special dialog box, check the box Transpose and click OK. This will paste the copied data transposed.

And voila! Take a look at the desired result after following this simple method.

• By following the same process, we can reverse the result. That means we can get back to our original dataset again.
• Here, we copied cells in the B10:F15 range and pasted them using the Paste Special feature as we did it before in this method.

2. Utilize Find & Replace Feature to Make Swapping Dynamic

Another way to accomplish this task is by utilizing the Find & Replace feature, which searches for a specific value and substitutes it with another. The advantage of this method over the previous one is that whenever we make a change to the original dataset, the same value in the swapped ones gets updated automatically.

• Firstly, select and copy the entire dataset. Then, bring the Paste Special dialog box following the steps in Method 1.
• Here, click on the Paste Link button.

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

• Now, highlight the pasted range (B10:G14) and navigate to the Home tab. Then, select the Find & Replace dropdown >> Replace option. Alternatively, you can use CTRL + H keyboard shortcut to replicate the task.

• In the Find and Replace dialog box, write = and xx in the Find what and Replace with box respectively. Next, tap on Replace All.

It’ll return a message box with the message “All done. We made 30 replacements.” Also, notice that xx is present in the place of the “=” sign in each cell of the selected range.

• Just, click OK.

• Now, copy the cells in the B10:G14 range and we want to paste it into cell B16.
• So, go to this cell and right-click on the mouse. Then, select Transpose (T) as Paste Options on the context menu.

You’ll get those values in a transposed position like the following image.

• Again, select the newly pasted range and press CTRL + H to open the Find and Replace dialog box.

• In the wizard, now write the inverse of the previous step. Write xx and = in the Find what and Replace with boxes respectively. Simply, click on Replace All.

And the result is before your eyes.

After a bit of formatting and hiding some rows, the final result is available.

Follow the video to get an insight into its dynamic feature.

3. Swap Columns and Rows for Large Datasets Using Power Query Editor

The capability to swap columns and rows is also available in the Power Query Editor. This feature is particularly useful for importing data from diverse sources and modifying it according to our needs. Therefore, the Power Query offers an additional approach to swap columns and rows.

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

• Instantly, it will open the Create Table dialog box showing the selected table range. Just, click OK here.

• In the Power Query Editor window, select Transform >> Transpose on the Table group of commands.

See the change in the table. It’s transposed now.

• Then, select Use First Row as Headers to get rid of the excess row at the top of the table.

Now, it’s time to bring back the data to the worksheet.

• Under the Home tab ribbon, click on the Close & Load dropdown >> Close & Load To… option.

• In the Import Data dialog box, you can choose your output destination. As we want to get them in the same worksheet, we selected the Existing worksheet and gave the cell reference of B11. Then, click OK.

That’s all there is to it! Here’s what your data will look like after swapping columns and rows in Excel.

4. Use TRANSPOSE Function to Update Data Automatically

Swapping the columns and rows of data can be a tedious task, especially when dealing with large datasets. However, with the TRANSPOSE function in Excel, you can complete this process with just a few clicks.

• In cell B10, write the following formula and press ENTER.

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

• Just apply some formatting and see the final result in the following image.

5. Swap Columns and Rows Through OFFSET Function

In this section, we will use the OFFSET function to swap columns and rows in Excel.

• Write the serial numbers from 0 to the (total row number-1) or (5 – 1) or 4 (as there are a total of 5 rows) to the top of the area where we will paste the data and use the serial numbers from 0 to the (total column number-1) or (6 – 1) or 5 (as here total column is 6) to the left of the designated area.

• Use the following formula in cell B11 and press ENTER.

• You can use the Fill Handle feature to copy the formulas in other cells to get results.

• Just apply a bit of formatting.

• Here, we selected cell B10 and in the formula bar, it shows a value of 0. But, in the worksheet cell, it isn’t visible.

You can hide those extra numbers around the output cells by changing their font color to white. That’s how you can conceal these values.

6. Use VLOOKUP Function with Some Manual Inputs

Here, we’ll use the VLOOKUP function to swap columns and rows in Excel.

• First, we manually transposed the first column and first row and wrote the values in the first row and first column in the output range.

• Then, in cell C11, use the following formula and press ENTER.

It’s that easy! Here’s what your data will look like after swapping columns and rows using this method.

7. Swap Columns and Rows Using INDEX Function

In this method, we’ll use the INDEX function with a combination of COLUMN and ROW functions. Though this method is quite more complex than the previous ones, you can learn it for future usage.

• Write the following formula in cell B10 and press ENTER.

With some formatting adjustments, the desired output will resemble the following.

8. Combine INDIRECT and ADDRESS Functions to Swap Columns and Rows

Combining the INDIRECT and ADDRESS functions is one effective approach to swapping columns and rows in Excel. By using these functions in conjunction, users can efficiently transpose data without the need for manual adjustments.

• Apply the following formula in cell B10. Also, use AutoFill to get other outputs.

9. Applying VBA Code to Swap Columns and Rows for Large Datasets in Excel

Although using formulas is a quick way, it can be difficult to interpret. Furthermore, if you often need to swap columns and rows quickly and efficiently, then you may consider the VBA code below.

• First, move to the Developer tab, then click on the Visual Basic button in the Code group.

It launches the Microsoft Visual Basic for Applications window.

• Now, click the Insert tab and choose Module from the list. We get a small Module window to insert our VBA code.

• Here’s the working code to do the task. Paste this code into the 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

• To run the code, go to the Developer tab >> Macros option on the Code group.

• Then, select the following macro and click on the Run button.

• Stick to the following video to understand the remaining process.

How to Switch Between Rows in Excel

Here, we want to switch Row 5 (Full Name) between rows 7 and 8. Basically, it will get row number 7.

• Select the data in Row 5 (B5:G5).
• Position your cursor until it resembles a universal arrow key, as illustrated below.

• Next, drag the cursor downwards until it reaches the top of Row 8. Hold down the SHIFT key and release the column.

The result is before your eyes.

How to Swap Columns in Excel

Similarly, if we want to swap Column D between columns F and G, what should we do? Here, we’ll do it like the previous method also.

You can see that the third column of the dataset gets swapped at the position of the fifth column in the dataset.

Benefits of Swapping Columns and Rows in Excel

Swapping the rows and columns in Excel can bring several advantages, which include:

• Enhanced Readability: When data is presented in a lengthy horizontal format, it can become arduous to comprehend and scrutinize. Swapping the columns and rows can modify the data into a vertical format, making it simpler to read and interpret.
• Better Data Arrangement: If the data you are working with has row headers instead of column headers, it might pose a challenge to filter or sort the data efficiently. In such a situation, swapping the rows and columns can help restructure the data, which can simplify the filtering and sorting process.
• Streamlined Analysis: Reversing the data by swapping the rows and columns can simplify the process of analyzing trends or patterns in the data. For example, if you possess sales data with years indicated in columns and product categories in rows, reversing the data can make it easier to compare sales by product category across different years.
• Improved Visual Representation: Swapping the rows and columns can also boost the visual representation of the data, particularly when working with a voluminous dataset. The process of reversing the data can make it more compressed and manageable to view without the need for scrolling.

Things to Remember

• Before you start swapping columns and rows, it is essential to make a backup copy of your data.
• Choose the method that works best for your specific situation.
• When swapping columns and rows, make sure to pay attention to the headers. If your data has row headers instead of column headers, make sure to adjust the headers accordingly after swapping the data.
• After swapping columns and rows, double-check your data for errors.

Frequently Asked Questions

1. How can I swap columns and rows in Excel for a large dataset?

For a large dataset, you can use Power Query or VBA macros to automate the process.

2. What are some common mistakes to avoid when swapping columns and rows in Excel?

Some common mistakes to avoid include selecting the wrong range of cells, forgetting to include all the necessary data in the range, failing to choose the correct options in the Paste Special dialog box, and accidentally overwriting existing data.

3. Is it possible to undo swapping columns and rows in Excel?

Yes, it is. Press Ctrl+Z or click the Undo button to undo the last action. If you have saved the workbook, you can also close it without saving it to revert to the previous version.

4. Can I swap columns and rows in Excel on a mobile device, such as a smartphone or tablet?

Yes, you can. However, the process may be slightly different depending on the version of Excel you are using and the device you are using it on. Look for options such as Transpose or Rotate in the toolbar or menu options to swap columns and rows.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly and will help you practice yourself.

Conclusion

In conclusion, we know how to swap columns and rows in Excel. This is a useful technique that can help you better organize and analyze your data. With the various methods described in this article, you can swap columns and rows in Excel in a way that best suits your needs and improves its readability, organization, and visual presentation.

Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Swap Non-Adjacent Cells in Excel
• How to Swap Text in Excel