How to Switch Rows and Columns in Excel (5 Methods)

If you want to switch rows and columns in Excel, you can do this manually or automatically in multiple ways depending on your purpose. In the following article, we would discuss 5 methods of switching rows and columns in Excel. Go along with the article and find your best method.


Download Practice Workbook

You are recommended to download the Excel file and practice along with it.


5 Methods to Switch Rows and Columns in Excel

Let’s say you have the following dataset of the Market share of the smartphone companies. We will use this dataset to demonstrate how to switch rows and columns in Excel.

5 Methods to Switch Rows and Columns in Excel

Read the following article in detail and choose the best solution that matches your intent.


1. Switch Rows and Columns by Paste Special (Transpose)

Using the Paste Special feature is a quick way to switch rows and columns in Excel. Choose a location where you want to paste the transposed table. Make sure that there is plenty of room to paste your data. The new table will entirely overwrite any data/formatting that’s already there. Follow the steps below to switch rows and columns.

Steps:

  • Select the range of cells B4:G9 and press Ctrl+C.

Switch Rows and Columns by Paste Special

  • Right-click over the top-left cell of where you want to paste the transposed table, we choose Cell B11 in this case, then choose Transpose.

Switch Rows and Columns by Paste Special

  • You can see the data is switched now.

Switch Rows and Columns by Paste Special

Read More: How to Transpose Column to Multiple Rows in Excel (6 Methods)


2. Use Transpose Function to Switch Rows and Columns

The TRANSPOSE function is a multi-cell array formula. It means we have to predetermine how many rows and columns we’re gonna need and select that much area on the sheet.

In the above example, we have a 6×6 dataset in range B4:G9. We need to select a 6×6 empty cell area to transpose the data.

Steps:

  • Select B11:G16. On the formula bar, type the formula:
=TRANSPOSE(B4:G9)

Use Transpose Function to Switch Rows and Columns

  • Press Ctrl+Shift+Enter. You can see the data is switched now.

Use Transpose Function to Switch Rows and ColumnsNotes & Tips:

  • The transposed data is still linked with the original data. Be careful to change the original data. Whenever you change data in the original data, it will be reflected in the transposed data too.
  • If you have a current version of Microsoft 365, then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, use Ctrl+Shift+Enter.

Read More: How to Transpose Multiple Columns to Rows in Excel


3. Using Cell Reference to Switch Rows and Columns

We can switch rows and columns using cell references. In the above example, we have a 6×6 dataset in range B4:G9. We need a 6×6 empty cell area to transpose the data.

Steps:

  • Select an empty Cell B11. Type in a reference prefix, say ‘RR’, and then the location of the first cell we want to transpose which is B4.

Using Cell Reference to Switch Rows and Columns

  • In Cell B12, type in the same prefix ‘RR’ and then the cell location to the right of the one we used in the previous step. For our purposes, that would be cell C4, which we’ll type in as RRC4. Similarly, type in the references in the cells below as well.

Using Cell Reference to Switch Rows and Columns

  • Select range of Cells B11:B16. Fill the rest of the cells by dragging the Autofill horizontally to Column G.

Using Cell Reference to Switch Rows and Columns

The rest of the cells should be auto-filled.

  • Press Ctrl+H on your keyboard to bring up the Find and Replace Into the Find what and Replace with field, type in prefix RR, and then =  into the field. Click Replace All.

Using Cell Reference to Switch Rows and Columns

  • A pop-up will show “All done. We made 36 replacements.” Click OK.

You can see the data is switched now.

Read More: How to Convert Multiple Rows to Columns in Excel (9 Ways)


Similar Readings


4. Using VBA Macros to Switch Rows and Columns

In this method, we will convert multiple rows to columns in Excel using VBA macros.

Steps:

  • Go to the Developer tab > Visual Basic.

Using VBA Macros to Switch Rows and Columns

  • In the Visual Basic Editor, go to Insert > Module.

Using VBA Macros to Switch Rows and ColumnsA new module will pop up. Copy the following script.

Sub SwitchRowsToColumns()
    Dim SourceRng As Range
    Dim DestRng As Range
 
    Set SourceRng = Application.InputBox(Prompt:="Select the array to rotate", Title:="Switch Rows to Columns", Type:=8)
    Set DestRng = Application.InputBox(Prompt:="Select the cell to insert the rotated columns", Title:="Switch Rows to Columns", Type:=8)
 
    SourceRng.Copy
    DestRng.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
 
End Sub
  • Paste the script into the window and save it with Ctrl+S.

Using VBA Macros to Switch Rows and Columns

  • Now, Close the Visual Basic Editor. Go to Developer > Macros and you’ll see your SwitchRowsToColumns macro. Click Run.

A Switch Rows to Column window will pop up asking to select the array.

  • Select the array B4:G9 to rotate. Click OK.

Again, the pop us will ask to select the first to insert the rotating columns.

  • Select Cell B11. Click OK.

Now, You can see the data is switched now.

Read More: Excel Macro: Convert Multiple Rows to Columns (3 Examples)


5. Switch Rows and Columns using Power Query

Power Query is another powerful tool available for Excel users which can be used to transpose rows to columns. If you’re working with Excel 2010 or Excel 2013, you need to explicitly download and install the Power Query add-in. You’ll find Power Query on the Data tab in Excel 2016 and upper versions.

Carefully follow the steps below to transpose rows to columns using Power Query.

Steps:

  • Select the range of cells B4:G9 to convert rows to columns in Excel.

Switch Rows and Columns using Power Query

  • Go to the Power Query tab, and select From Table/Range.

A pop-up will show asking the range. Click OK.

The following table will show up in the Power Query Editor.

  • In the Power Query Editor > Go to Transform tab > Select Use First Row as Headers > Select Use Headers as First Row

Switch Rows and Columns using Power Query

  • Click on Transpose under the Transform tab.

Switch Rows and Columns using Power Query

You can see the data is switched now.

Read More: How to Switch Rows and Columns in Excel Chart (2 Methods)


Fix Issues with Transposing Rows and Columns

1. Overlap Error

The overlap error will occur if you try to paste the transposed range into the area of the copied range. Be careful about this error and choose a cell that is not in the copied range of cells.

2. #VALUE! Error

If you implement the TRANSPOSE formula in Excel by pressing just Enter, You may see this #VALUE! Error. To avoid this error, be sure to press Ctrl+Shift+Enter.

Note:

If you have a current version of Microsoft 365, then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. In older versions, the formula must be entered as a legacy array formula by first selecting the output range, inputting the formula in the top-left-cell of the output range, then pressing Ctrl+Shift+Enter to confirm it.


Conclusion

In this article, we showed five simple methods to switch rows and columns in Excel. Practice all of them along with the given practice workbook and find which method best suits your case. Hope you find the above methods useful and comment on our website ExcelDemy.com if you need further clarifications or have something to add.


Further Readings

Robiul Hossain

Robiul Hossain

Hello, I am Robiul. I’ve completed my BSc in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology (BUET). I have a keen interest in Programming and Data science. I really love to solve problems that help lots of people in their day to day life. Currently, I am working with a highly skilled and motivated Technical Writing team of ExcelDemy. We are committed to bringing you the best possible results of your Microsoft Excel problems. Stay with us and enjoy the best Excel experience.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo