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.
How to Switch Rows and Columns in Excel: 5 Methods
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.
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.
- 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.
- You can see the data is switched now.
Read More: How to Switch Rows and Columns in Excel Chart
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 the 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:
- Press Ctrl+Shift+Enter. You can see the data is switched now.
Notes & 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.
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 the 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.
- 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.
- Select range of Cells B11:B16. Fill the rest of the cells by dragging the Autofill horizontally to Column G.
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.
- A pop-up will show “All done. We made 36 replacements.” Click OK.
You can see the data is switched now.
Read More: Excel VBA: Get Row and Column Number from Cell Address
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.
- In the Visual Basic Editor, go to Insert > Module.
A 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.
- 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 VBA to Set Range Using Row and Column Numbers
5. Switch Rows and Columns using Power Query
Power Query is another powerful tool available for Excel users that 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.
- 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
- Click on Transpose under the Transform tab.
You can see the data is switched now.
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, and then pressing Ctrl+Shift+Enter to confirm it.
Read More: [Fixed!] Rows and Columns Are Both Numbers in Excel
Download Practice Workbook
You are recommended to download the Excel file and practice along with 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 your thoughts in the following section if you need further clarifications or have something to add.
