How To Switch Data From Rows To Columns In Excel

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 commodity, we would discuss 5 methods of switching rows and columns in Excel. Go along with the commodity and find your best method.

Download Practice Workbook

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

5 Methods to Switch Rows and Columns in Excel

Allow's say you accept the following dataset of the Market share of the smartphone companies. Nosotros will utilize this dataset to demonstrate how to switch rows and columns in Excel.

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

ane. Switch Rows and Columns past Paste Special (Transpose)

Using the Paste Special feature is a quick mode to switch rows and columns in Excel. Choose a location where yous want to paste the transposed table. Brand sure that at that place is plenty of room to paste your data. The new table will entirely overwrite whatever 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 yous desire to paste the transposed table, nosotros choose Jail cell B11 in this case, then cull Transpose.

Y'all can see the data is switched now.

2. Use Transpose Function to Switch Rows and Columns

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

In the above instance, nosotros have a 6×6 dataset in range B4:G9. We demand to select a half-dozen×6 empty cell area to transpose the information.

Steps:

Select B11:G16. On the formula bar, type the formula:

Press Ctrl+Shift+Enter. Y'all can see the information is switched at present.

Notes & Tips:

The transposed information is nonetheless linked with the original data. Be careful to change the original data. Whenever y'all modify data in the original information, 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 pinnacle-left-jail cell of the output range, then press ENTER to confirm the formula every bit a dynamic array formula. Otherwise, utilize Ctrl+Shift+Enter.

3. Using Cell Reference to Switch Rows and Columns

Nosotros 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×half dozen empty cell surface area to transpose the information.

Steps:

Select an empty Cell B11. Type in a reference prefix, say 'RR', so the location of the beginning cell nosotros want to transpose which is B4.

In Prison cell B12, type in the aforementioned prefix 'RR' and then the cell location to the right of the one we used in the previous stride. For our purposes, that would exist jail cell C4, which we'll blazon in as RRC4. Similarly, blazon in the references in the cells below also.

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

The rest of the cells should be auto-filled.

Printing Ctrl+H on your keyboard to bring up the Observe and Replace Into the Find what and Supervene upon with field, type in prefix RR, and so = into the field. Click Replace All.

A pop-up volition show "All done. We made 36 replacements." Click OK.

Yous can run into the data is switched at present.

Like Readings

How to Add together Rows and Columns in Excel (3 Easy Methods)
Move Row/Column in Excel Without Replacing Existing Information (iii All-time Ways)
How to Select Every Other Row in Excel(6 Ways)

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 Programmer tab > Visual Basic.

In the Visual Basic Editor, get to Insert > Module.

A new module volition pop up. Re-create the following script.

          Sub SwitchRowsToColumns()     Dim SourceRng Equally Range     Dim DestRng As Range       Set SourceRng = Awarding.InputBox(Prompt:="Select the array to rotate", Title:="Switch Rows to Columns", Blazon:=viii)     Set up DestRng = Awarding.InputBox(Prompt:="Select the cell to insert the rotated columns", Title:="Switch Rows to Columns", Type:=viii)       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.

At present, Close the Visual Basic Editor. Become to Developer > Macros and you'll run across your SwitchRowsToColumns macro. Click Run.

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

Select the assortment B4:G9 to rotate. Click OK.

Again, the pop united states volition ask to select the outset to insert the rotating columns.

Select Jail cell B11. Click OK.

Now, You can see the information is switched now.

5. Switch Rows and Columns using Ability 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 detect 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.

Become to the Ability Query tab, and select From Table/Range.

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

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

In the Power Query Editor > Go to Transform tab > Select Use Beginning Row every bit Headers > Select Use Headers as Outset Row

Click on Transpose under the Transform tab.

You can run into the data is switched at present.

Fix Bug with Transposing Rows and Columns

1. Overlap Error

The overlap error volition occur if you lot try to paste the transposed range into the area of the copied range. Be careful about this fault 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 simply Enter, Yous may see this #VALUE! Error. To avert this mistake, be sure to press Ctrl+Shift+Enter.

Annotation:

If you accept a electric current version of Microsoft 365, then yous can input the formula in the top-left-jail cell of the output range, and then press ENTER to confirm the formula as a dynamic array formula. In older versions, the formula must be entered every bit a legacy array formula by beginning selecting the output range, inputting the formula in the top-left-prison cell of the output range, so pressing Ctrl+Shift+Enter to ostend it.

Determination

In this article, we showed five elementary methods to switch rows and columns in Excel. Do all of them forth with the given practice workbook and observe which method best suits your instance. Promise y'all notice the above methods useful and comment on our website ExcelDemy.com if you demand further clarifications or have something to add.