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

The dataset below shows the market share of smartphone companies.

5 Methods to Switch Rows and Columns in Excel


Method 1 – Using the Paste Special (Transpose)

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 where you want to paste the transposed table. (We chose cell B11, then clicked Transpose.)

Switch Rows and Columns by Paste Special

  • The data is now switched.

Switch Rows and Columns by Paste Special


Method 2 – Using the Transpose Function 

Steps:

  • Select cell range B11:G16. In the formula bar, type the following formula:
=TRANSPOSE(B4:G9)

Use Transpose Function to Switch Rows and Columns

  • Press Ctrl+Shift+Enter. The data is now switched.

Use Transpose Function to Switch Rows and ColumnsNotes:

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

Method 3 – Using Cell Reference 

Steps:

  • Select an empty cell (e.g., B11).
  • Enter a reference prefix, say ‘RR,’ and the location of the first cell we want to transpose, B4.

Using Cell Reference to Switch Rows and Columns

  • Enter the same prefix ‘RR’ in cell B12 in the cell to the right of the one we used in the previous step. (i.e.,C4, which we’ll type in as RRC4.)
  • Continue to enter the references in the cells below.

Using Cell Reference to Switch Rows and Columns

  • Select the 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 to bring up Find and Replace. Enter prefix RR into Find what and (=) into Replace with. Click Replace All.

Using Cell Reference to Switch Rows and Columns

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

The data is switched.

Read More: Excel VBA: Get Row and Column Number from Cell Address


Method 4 – 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 press Ctrl+S to save.

Using VBA Macros to Switch Rows and Columns

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

The pop-up will ask you to select the first to insert the rotating columns.

  • Select Cell B11. Click OK.

The data is switched.

Read More: Excel VBA to Set Range Using Row and Column Numbers


Method 5 – 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 ask the range. Click OK.

The following table will appear 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

The data is switched.


How to Fix Issues With Transposing Rows and Columns

Overlap Error

If you try to paste the transposed range into the area of the copied range, an overlap error will occur. Choose a cell that is not in the copied range of cells.

#VALUE! Error

If you implement the TRANSPOSE formula in Excel by pressing Enter, you may see this #VALUE! Error. To avoid this, Press Ctrl+Shift+Enter.

Note: If you have a current version of Microsoft 365, you can input the formula in the top-left cell of the output range and press Enter to confirm it 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 the Practice Workbook

You can download the workbook to practice.


Further Readings


<< Go Back to Rows and Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo