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:

=TRANSPOSE(B2:B5)

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 Posts
How to Count Row That Contain Specific Value in Excel
count row that contain specific value3

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
transpose rows and columns11

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

Excel DSTDEVP Function
excel dstdevp examples1

This post will guide you how to use Excel DSTDEVP function with syntax and examples in Microsoft excel. Description The Excel DSTDEVP returns the standard deviation of a population based on the entire population of numbers in a column or ...

Excel DSTDEV Function
excel dstdev examples1

This post will guide you how to use Excel DSTDEV function with syntax and examples in Microsoft excel. Description The Excel DSTDEV returns the standard deviation of a population based on a sample of numbers in a column or database ...

Excel DMIN Function
excel dmin function example1

This post will guide you how to use Excel DMIN function with syntax and examples in Microsoft excel. Description The Excel DMIN returns the minimum value from a database or a column of a list that matches the specified conditions. ...

Excel DPRODUCT Function
excel dproduct examples1

This post will guide you how to use Excel DPRODUCT function with syntax and examples in Microsoft excel. Description The Excel DPRODUCT returns the product of values from a set of records that match criteria that you specify. The DPRODUCT ...

Excel XIRR Function
excel xirr examples1

This post will guide you how to use Excel XIRR function with syntax and examples in Microsoft excel. Description The Excel XIRR function returns the internal rate of return for a series of cash flows that is not necessarily periodic. ...

Excel VDB Function
excel vdb examples1

This post will guide you how to use Excel VDB function with syntax and examples in Microsoft excel. Description The Excel VDB function calculates the depreciation of an asset for a specified period based on the double declining balance method. ...

Excel SYD Function
excel syd examples1

This post will guide you how to use Excel SYD function with syntax and examples in Microsoft excel. Description The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric ...

Excel SLN Function
excel sln examples1

This post will guide you how to use Excel SLN function with syntax and examples in Microsoft excel. Description The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it ...

Sidebar