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

Get FREE Advanced Excel Exercises with Solutions!

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!

how to swap columns and rows 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.

dataset of employee information of an organization

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, sort and filter excel 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.

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

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.

copying the dataset

Note: In the case of a larger dataset, it’s hectic to select the entire dataset by clicking and dragging the mouse cursor. Hence, click on any cell inside the dataset and press CTRL + A to select the whole dataset swiftly.
  • Then, right-click on the destination cell (cell B10) and select the Paste Special option from the context menu.

clicking on Paste Special option

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

checking the Transpose option

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

columns and rows swapped in Excel

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

again transposed to previous form


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.

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

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

columns and rows swapped without formatting

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

opening Find & Replace feature

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

replacing equal sign with xx in selected range in Excel

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.

equal signs in formulas got replaced with xx

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

copying data and pasting using Transpose as Paste options in excel

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

pasted as transposed data in Excel

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

opening find & replace dialog box again

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

again replacing all equal signs with xx

And the result is before your eyes.

swap columns and rows using Find & Replace feature

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

final look after applying formatting

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.

getting data from table or range

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

working on create table dialog box

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

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

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.

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

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.

clicking on close & Load option in Excel

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

defining output range of the data

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

swap columns and rows in the existing worksheet using power query feature 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.

The TRANSPOSE function allows users to switch the orientation of data, transforming columns into rows, and rows into columns, in a matter of seconds.
Remind that this function is available from Excel 2007 version. You cannot apply this function in the prior versions of Excel.
  • In cell B10, write 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: Make sure to press CTRL + SHIFT + ENTER if you are using any other versions except Excel 365 while working with the array formula.
  • Just apply some formatting and see the final result in the following image.

columns and rows swapped and added formatting in Excel

Note: This method is also dynamic. You can test it by changing any value in the source data range.

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.

writing serial number to get help in the process

  • Use the following formula in cell B11 and press ENTER.
=OFFSET($B$4,B$10,$A11)
  • You can use the Fill Handle feature to copy the formulas in other cells to get results.

using OFFSET function to swap columns and rows in Excel

  • Just apply a bit of formatting.

added formatting to look like the original data

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

serial numbers got hidden


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.

transposed data manually to start the process

  • Then, in cell 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

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.

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

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

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

added formatting in column headers


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.
=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): This function returns the column number of cell C5.
  • COLUMN($B$4): This function returns the column number of the cell B4. The dollar signs around the row and column reference indicate an absolute reference, meaning that the reference will not change if the formula is copied to other cells.
  • COLUMN(C5)-COLUMN($B$4): This calculates the number of columns between the reference cell (B4) and the target cell (C5).
  • ROW($B$4): This function returns the row number of cell B4.
  • ROW(C5)-ROW($B$4): This calculates the number of rows between the reference cell (B4) and the target cell (C5).
  • COLUMN(C5)-COLUMN($B$4)+ROW($B$4): This adds the number of columns and rows calculated in steps 3 and 4 to the row number of the reference cell.
  • ROW(C5)-ROW($B$4)+COLUMN($B$4): This adds the number of rows and columns calculated in step 5 to the column number of the reference cell.
  • ADDRESS(COLUMN(C5)-COLUMN($B$4)+ROW($B$4), ROW(C5)-ROW($B$4)+COLUMN($B$4)): This function 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))): This function converts the text string returned by the ADDRESS function into a reference to the target cell. The INDIRECT function allows the formula to reference a cell based on a text string.

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.

navigating to Developer tab

Note: By default, the Developer tab remains hidden. You can learn to enable the Developer tab by following this linked article.

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.

inserting new code module

  • 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
Note: For a deeper comprehension of the code, refer to the comments.

VBA code to swap columns and rows in Excel

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

clicking on Macros option

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

selecting macro to run in the Macro dialog box

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

swapping row in different position

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

selecting row to swap in excel

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

row got swapped in the desired position


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.

swapping column in different position

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

column got swapped in the desired position


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


<< Go Back to Swap Cells | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo