How to Transpose Columns to Rows in Excel (8 Easy Methods)

Everyone has their own point of view when visualizing and representing data. Some may feel comfortable working with data when they are in the horizontal direction, while others may prefer to work with them when they are vertically represented. So sometimes we need to change the overview of a whole large worksheet based on the user’s need. In Excel, you can do that quickly and efficiently. This article will show you how to transpose columns to rows in Excel with the 8 most effective and easy ways.


Download Practice Workbook

You can download the practice workbook from here.


8 Most Effective Methods to Transpose Columns to Rows in Excel

In the following article, we will describe the 8 most effective and easiest methods to transpose columns to rows in Excel. Here, we used Excel 365. You can use any available Excel version.


1. Using Paste Special Feature

The copy-and-paste method is the most frequently used method in Excel due to its simplicity to organize computerized documents. It is widely being used from simple modification to extensive modification in all kinds of digital tasks around the world. In this method, we will use the Copy-and-Paste method to transpose a single column to a single row.


1.1. Transposing a Single Column to a Single Row

Here, we will use the Copy and Paste method to transpose a single column to a single row.

Steps:

  • In the beginning, we will select cells B4:B10 >> press CTRL+C.
  • This will copy these cells.
  • Here, you can right-click on the selected cells and select Copy from the Context Menu.

Using Copy and Paste Feature to Transpose Single Column to Row In Excel

  • After that, select the cell that you want to have the first value of the row. Here, we selected cell B13.
  • Furthermore, right-click the mouse >> in the Paste Options section >> select Transpose (T), shown in the following picture.

  • Afterward, once you select the Transpose (T) Paste Option, it will transform the column into a row.
  • Therefore, you can see the transposed row in the following picture.

Read More: Excel Paste Transpose Shortcut: 4 Easy Ways to Use


1.2. Transposing Multiple Columns to Multiple Rows

Here, we will use the Copy and Paste method to transpose multiple columns to multiple rows.

Steps:

  • In the beginning, we will select cells B4:C10 >> press CTRL+C.
  • This will copy these cells.
  • Here, you can right-click on the selected cells and select Copy from the Context Menu.

Use of Copy and Paste Feature to Transpose Multiple Columns to Rows In Excel

  • After that, select the cell that you want to have the first value of the row. Here, we selected cell B13.
  • Furthermore, right-click the mouse >> in the Paste Options section >> select Transpose (T), shown in the following picture.

  • Afterward, once you select the Transpose (T) Paste Option, it will transform the column into a row.
  • Hence, you can see the transposed rows in the following picture.

Read More: VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)


2. Using TRANSPOSE Function

In this method, we will use the TRANSPOSE function to transpose columns to rows in Excel.


2.1. TRANSPOSE Function for Converting a Single Column to a Single Row

Here, we will use the TRANSPOSE function for a single column.

Steps:

  • In the first place, we will type the following formula in cell B13.
=TRANSPOSE(B4:B10)

Using TRANSPOSE Function to Transpose Single Column to Row In Excel

Formula Breakdown

  • TRANSPOSE(B4:B10)the TRANSPOSE function converts the column to a row.
  • B4:B10 → is the array.
  • After that, press ENTER.
Note: If you are not using Excel 365, you have to press CTRL+SHIFT+ENTER since this is an array formula.
  • Therefore, you will see the transposed row.

Read More: How to Convert Single Columns to Rows in Excel with Formulas

Similar Readings


2.2. TRANSPOSE Function for Conversion of Multiple Columns to Multiple Rows

Here, we will use the TRANSPOSE function to transpose multiple columns to rows in Excel.

Steps:

  • In the beginning, we will type the following formula in cell B13.
=TRANSPOSE(B4:C8)

Use of TRANSPOSE Function to Transpose Multipe Columns to Rows In Excel

  • Afterward, press ENTER.
Note: If you are not using Excel 365, you have to press CTRL+SHIFT+ENTER since this is an array formula.
  • As a result, you will see the transposed rows.

Transpose Columns to Rows In Excel

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


3. Combining OFFSET, COLUMNS, and ROWS Functions

In this method, we will use the combination of OFFSET, COLUMNS, and ROWS functions to transpose a single column to multiple rows in Excel.

Steps:

  • In the beginning, we will type the following formula in cell C17.
=OFFSET($B$5, COLUMNS($B: B)-1+(ROWS($5:5)-1)*5,0)

Combining OFFSET, COLUMNS, ROW Function to Transpose Columns to Rows In Excel

Formula Breakdown

  • The OFFSET function gives out a reference to a range which is a particular number of rows and columns from a cell or a number of cells.
  • The COLUMNS function gives out the number of columns in a range.
  • The ROWS function finds the number of rows in a range.
  • OFFSET($B$5,COLUMNS($B:B)-1+(ROWS($5:5)-1)*5,0) → becomes
  • Output: 1
  • At this point, press ENTER.
  • Therefore, you can see the result in cell C17.
  • Furthermore, we will drag the formula horizontally up to cell G17 using the Fill Handle tool.

Dragging Formula Horizontally to Transpose Columns to Rows In Excel

  • As a result, you can see one transposed row.
  • Next, we will drag down the formula of cell C17 to cell C18 using the Fill Handle tool.
  • This will give us another transposed row.

  • Therefore, you can see a value in cell C18.
  • Moreover, we will drag the formula horizontally up to cell G18.

  • Hence, you can see two transposed rows.

Read More: Transpose Multiple Columns into One Column in Excel (3 Handy Methods)


4. Applying Cell Reference to Transpose Columns to Rows in Excel

Transposing columns to rows using cell referencing is a very efficient way while work with a large amount of data.
The steps of implementing cell referencing to transpose columns to rows are described below.

Steps:

  • First of all, we will simply type saB5 in cell C13.
  • Here, we include sa with the cell reference, you can type anything according to your choice.
  • In addition, we will drag the value horizontally up to cell H13 with a Fill Handle tool.

Applying Cell Reference to Transpose Columns to Rows In Excel

  • Therefore, you can see the Odd Number row.
  • Next, we will type saC5 in cell C14.
  • In addition, we will drag the formula horizontally with a Fill Handle tool.

  • Therefore, you can see two rows.

  • Next, we will replace sa with “=” so that the cells extract the value from the columns.
  • To do so, we will go to the Home tab >> go to the Editing group.
  • Then, from Find & Select >> select Replace.

  • At this point, a Find and Replace dialog box will appear.
  • Afterward, in the Find what box, type sa.
  • Moreover, in the Replace with box, type =.
  • Along with that, click on Replace All.

Use of Find and Replace Tool to Transpose Columns to Rows In Excel

  • At this point, a confirmation dialog box will pop up.
  • Click OK.

  • After that, close the Find and Replace dialog box.
  • Therefore, you can see that rows now have the values of the columns.
  • Hence, you can transpose columns to rows.

Transpose Columns to Rows In Excel

Read More: Excel Transpose Formulas Without Changing References (4 Easy Ways)


5. Merging INDIRECT and COLUMN Functions to Transpose Columns to Rows in Excel

In this method, we will use the combination of INDIRECT and COLUMN functions to transpose columns to rows in Excel.

Steps:

  • In the first place, we will type the following formula in cell F4.
=INDIRECT("b"&COLUMN()-1)

Merging INDIRECT and COLUMN Functions to Transpose Columns to Rows In Excel

Formula Breakdown

  • The INDIRECT function finds the reference to a particular text string.
  • The COLUMN function finds the column number of a particular cell reference.
  • INDIRECT(“b”&COLUMN()-1) →becomes
  • Output: 1
  • At this moment, press ENTER.
  • Therefore, you can see the result in cell F4.
  • Furthermore, we will drag the formula horizontally up to cell I4 with the Fill Handle tool.

  • Therefore, you can see the complete Odd Number row.

  • After that, we will type the following formula in cell F5.
=INDIRECT("c"&COLUMN()-1)

  • At this moment, press ENTER.
  • Therefore, you can see the result in cell F5.
  • Furthermore, we will drag the formula horizontally up to cell I5 with the Fill Handle tool.

  • As a result, you can see the transposed rows.


6. Combining INDEX and COLUMN Functions to Transpose Columns to Rows in Excel

In this method, we will merge the INDEX and COLUMN functions to transpose columns to rows in Excel.

Steps:

  • In the beginning, we will type the following formula in cell F4.
=INDEX($B$4:$C$8, COLUMN()-4,1)

Combining INDEX and COLUMN Functions to Transpose Columns to Rows In Excel

Formula Breakdown

  • The INDEX function finds a value with a table or range.
  • The COLUMN function finds the column number of a particular cell reference.
  • INDEX($B$4:$C$8,COLUMN()-4,1) → becomes
  • Output:1
  •  At this moment, press ENTER.
  • Therefore, you can see the result in cell F4.
  • Furthermore, we will drag the formula horizontally up to cell I4 with the Fill Handle tool.

  •  Therefore, you can see the complete Odd Number row.
  • Afterward, we will type the following formula in cell F5.
=INDEX($B$4:$C$8,COLUMN()-4,1)

  • At this point, press ENTER.
  • As a result, you can see the result in cell F5.
  • Moreover, we will drag the formula horizontally up to cell I5 with the Fill Handle tool.

  • Hence, you can see two transposed rows.

Transpose Columns to Rows In Excel


7. Use of Power Query to Transpose Columns to Rows in Excel

In this method, we will use the Power Query to transpose columns to rows in Excel.

Steps:

  • In the first place, we will select the entire dataset by selecting cells B4:C11.
  • After that go to the Data tab >> select From Table/Range.

Use of Power Query to Transpose Columns to Rows In Excel

  • At this moment, a Create Table dialog box will appear.
  • Here, make sure My table has headers marked.
  • Then, click OK.

  • Therefore, you can see the created Power Query of the dataset.
  • Then, we will go to the Transform tab >> select Transpose.

  • Therefore, you can see the transposed rows in the Power Query.

  • Now, it is time to shift the transposed dataset to our Worksheet.
  • To do so, we will go to the Home tab.
  • Then, from the Close & Load group >> select Close & Load To.

Loading the Dataset to Existing Worksheet to Transpose Columns to Rows In Excel

  • At this point, an Import Data dialog box will pop up.
  • Here, we want to import data in the existing worksheet, therefore, we select Existing Worksheet.
  • Moreover, we select cell B14 for the location.
  • Then, click OK.

  • Hence, you can see the transposed rows in the existing worksheet.


8. Inserting VBA Macro to Transpose Columns to Rows in Excel

This is a quick and handy procedure to transpose columns to rows in Excel.

Steps:

  • First, go to the Developer tab >> select Visual Basic.
  • This will bring out a VBA Editor window.
  • You can also press ALT+F11 to bring out the VBA Editor window.

Inserting VBA to Transpose Columns to Rows In Excel

At this point, a VBA Editor window will appear.
Then, from the Insert tab >> select Module.

  • Furthermore, we will type the following code in the Module.
Sub Transpose_Columns_to_Rows()
Dim work_range As Range
Dim target_range As Range
Set work_range = Application.InputBox _
(Prompt:="Select the columns", Type:=8)
Set target_range = Application.InputBox _
(Prompt:="Select location cell", Type:=8)
work_range.Copy
target_range.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub

  • Then, Save the Code.
  • Now, return to the Worksheet, go to the Developer tab >> select Macro.

  • Next, select the Sub-procedure of the code >> click on Run.

  • Therefore, an Input window will appear.
  • Then, we will select columns B4:B9 in the Select the column box >> click OK.

  • In addition, another Input window will appear.
  • Here, we will select cell B12 in the Select the location box >> click OK.

Selecting Location Cell to Transpose Columns to Rows In Excel

  • Therefore, you can see a transposed row in cell B12:G12.

  • Furthermore, we again run the code for the Even Number column.
  • Therefore, you can see two transposed rows.


Things to Remember

  • While using the Transpose function, if you are not using Excel 365, then don’t hit only ENTER, hit CTRL + SHIFT+ ENTER.
  • Remember the Transpose function is not working with just a single data, it is working with a large amount of data so don’t forget to select a range of cells while transposing your dataset.

Do It Yourself

You can download the above Excel file and practice the explained methods.


Conclusion

Here, we show you 8 easy methods to transpose columns to rows in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo