How to Transpose in MS Excel 2016?

Transpose is an array function i.e. it is applicable to arrays. There are two methods to perform the transpose first is Paste Special method, second Transpose function. The drawback with Paste special method is that if we perform any change in the source data it does not reflect in the transposed data. But if you are using Transpose function and perform any change in the source data it will reflect in the transposed data also. So, let us begin How to Transpose in MS Excel 2016?

Steps to Transpose in MS Excel 2016:

Find out that rows and columns to which you want to transpose. Now, in the image below you can see, I have data that shows the collection from the year 2011-2015 in all months. Where months data lies in rows and years data lies in columns.

But I want the years to be in rows and months to be in columns. For this, I have to apply transpose to the data.
As discussed above there are two methods to transpose in Excel. Let us start with Paste special:

Paste Special:

Step 1: Select the complete data that you want to transpose and Copy it.

Step 2: Right click your mouse and reach Paste Options, and search for the Transpose (T) button. If you fail to find it there, you can also search it in Paste Special options that appear just below the Paste Options. click on Transpose (T) button.

That all, your data would be transposed.

Transpose (array) Function:

Step 1:  You can see that I have already copy pasted the row and columns heading. This is only to get an idea of how many cells the transposed data will require. Now select all the cells that would store the transposed data.

Step 2: Then type =TRANSPOSE( ; and select the data array that you want to transpose. You can observe that the =TRANSPOSE (Cell 1 : Cell N  i.e. Transpose function automatically fills the cell code.

Step 3: After that close the TRANSPOSE function bracket and press Cntrl+Shift+Enter. You will see that all the data is transposed.

Thank You. Hope the content is useful. Keep reading.