How to Transpose Data from Columns to Rows

This post explains that how to transpose data from columns to rows, or from rows to columns in excel. if you want to rotate the data in columns of a worksheet to rearrange it in rows, you can use the “Paste Special Transpose” option to switch rows to columns or vice verse. you can also use the TRANSPOSE function to achieve the same result.

Using Paste Special Transpose

To transpose the data in the range B2:B5 from columns to rows, refer to the following steps:

1# Select the range B2:B5 that you want to rearrange, then press CTRL+C to copy the selected Range.

using transpose feature1

2# Select Cell D2, then Right-click on the selected range, then click “Paste Special….”, the “Paste Special” window will appear.

using transpose feature2

3# checked “Transpose“. click OK.

using transpose feature3

4# let’s see the last result.

using transpose feature4

Using Transpose Function

If you want to transpose the range B2:B5 to D2:G2, just enter into the following array formula in Cell C1:


You need to select the range D2:G2, then enter the above formula in the formula bar. Then click “Ctrl”+”Shift”+Enter key to convert its as an array formula.

using transpose function1

Related Functions

  • Excel TRANSPOSE function
    Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel.The Excel TRANSPOSE Function syntax:=TRANSLATE (range) …
Related Posts

Excel TRANSPOSE Function

Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel. Its goal, as a component of the Excel lookup and reference functions, is to arrange data in the appropriate manner. To run ...

Extract or Filter Horizontal Data

You might have been through this kind of situation where you need to filter out the horizontal data from the list having few columns. I am also pretty sure about it that you might have chosen to do it manually, ...

Filter And Transpose Data From Horizontal To Vertical

This post will show you how to use Filter function and in combination with Transpose function to filter data from horizontal and transpose data as vertical in Microsoft Excel. You can refer to the below general formula based on TRANSPOSE ...

How to Count Row That Contain Specific Value in Excel

This post will guide you how to count rows that contain a specific text or number in Excel 2013/2016 or Excel office 365. How do I count the number of rows that contain a particular value while working with your ...

How to Transpose Rows and Columns in Excel

This post will guide you how to convert row data to columns in Excel. How do I convert columns to rows with Paste feature in Excel 2013/2016. Convert Rows to Columns Using Paste Special Convert Rows to Columns Using Formula ...