Switch or Transpose Row And Column Data in MS Excel

Sometime it is necessary to “Switching” or “Transposing” the orientation of data from row to column-wise or column to row-wise to make it easier for more understanding, presentable and effective by using Transpose option in MS Excel. I have an easiest example from which you can easily change orientation of your data. How to Switch or Transpose Row And Column Data in easy way. The transpose option is used to convert rows copied data into columns and columns copied data into rows.

Methods of Data Transpose in Excel:

1. By Using Paste Special -> Transpose2. By Using Transpose Function in Lookup & Reference category.

Must Read: Use Data Validation, Naming the range and VLookup Function

First Method by Transpose Option

Step 1: Select the range A1:C5 which you want to transpose. (Before Transpose)

Switch-or-Transpose-Row-And-Column-Data

Step 2: Press Ctrl + C or you can also click Copy button in Home Tab.

Step 3: Take cursor on new location in which you want to view the result. For Example: F1

Step 4: Click Paste button -> Click Transpose button or you can also select Transpose option in Paste Special dialog box.

How-To-Transpose-Data-in-MS-Excel-2013

Step 5: After choosing this option range (F1:J3) covert into rows to column wise and column to row wise data like this. (After Transpose)

How-To-Transpose-Data-in-Ms-Excel

Second Method by Transpose Function

You can also use Transpose function from Lookup and Reference category in Formula Tab. The TRANSPOSE function is also used to convert rows copied data into columns and columns copied data into rows.

Must Read: MS Excel : Vlookup Function with a Spreadsheet

Syntax of TRANSPOSE function:      = TRANSPOSE (array) array – It is a range of cells or values which you want to transpose. You have to take following steps to use this function

Step 1: Select the range F1:J3

Step 2: Click Formula >>> Lookup & Reference >>> Select Transpose Function Step 3: Select the range A1:C5 in Array box then press Ctrl + Shift + Enter Key

How-To-Transpose-Data-in-Ms-Excel

Step 4: The data of range A1:C5 transpose and shows in cells F1:J3.

How-To-Transpose-Data-in-Ms-Excel

I hope you like this post. Sent your important suggestions and any query in comment box.

Leave a Reply

Your email address will not be published. Required fields are marked *