Excel Paste Transpose

Paste Transpose in Excel

In excel, there are instances where you would have never imagined before, but if you are good at advanced skills, you will find a way out sort out. One such instance as a new user we face is “Transposing the data.” The word Transpose may find strange, but this will be the feature you may have to use more often than not to organize the data. So in this article, we will show you different ways of paste transposing the data in excel.

What is Transpose in Excel?

In Excel, we store data in rows and columns; it depends on the user to a user how they want to show the data. Some users may want to see more rows than columns, and some users may want to see more columns than rows, so in these cases, if we can change the position of the rows into columns and columns into rows.

So the process of swapping columns with rows is called Transpose in Excel.” Below is the image of the transposed data.

Transpose Data 1

So, now we will see how we can transpose by using different methods.

Two Ways to Paste Transposing Data in Excel

Mainly there are two ways we can use in excel to swap rows and columns; those two methods are as below.

You can download this Paste Transpose Excel Template here – Paste Transpose Excel Template

#1 – TRANSPOSE Function

If you look at the list of formulas in excel, we have a function called “TRANSPOSE” under the “Lookup & Reference” category. This is an array formula, so using this function, we can interchange the position of rows and columns of data.

  • For example, look at the below data.

Excel Paste Transpose 1

We need to transpose the above data, so to use the TRANSPOSE function, we need to do a little bit of work before, i.e., counting a number of rows and columns in the current table.

  • In the current table, we have two columns and five rows of data.

Excel Paste Transpose 1-1

Before we use the TRANSPOSE function, we need to select five columns and two rows. Yes, because we are transposing the data, we need to select the opposite number of rows and columns in transpose.

  • Because of a current number of rows, data will come to a column, and current columns data will come to rows, so in this case, we have 5 rows in the current table, so select 5 columns in the new table, and we have 2 columns in the current table, so select 2 rows in the new table.

Excel Paste Transpose Function 1-2

  • Look at the above picture; we have selected five columns and two rows (D1 to H2). Now open the TRANSPOSE function in the D1 cell.

Excel Paste Transpose Function 1-3

  • Choose the data range as the current table from A1 to B5.

Excel Paste Transpose Function 1-4

  • TRANSPOSE is an array formula in excel, so we need to close the formula by holding “Ctrl + Shift + Enter” together.

Excel Paste Transpose Function 1-5

There you go, we have got the transpose of our data.

#2 – Paste Special Method

If you have used paste special methods, then you know how good that tool is. Using the Paste Method, we can transpose the data quickly; in fact, quicker than the above TRANSPOSE method.

Paste Special Method 1

In order to use Paste Special method first, we need to copy the data range that we need to transpose, so copy the first table.

  • Now select the cell where you need to transpose the copied data, so we will choose D1 cell.

Paste Special Method 1-1

  • Now right click and hover on “Paste Special” to see paste special options.

Paste Special Method 1-2

  • From the above options, choose the method as “Transpose.”

Transpose Function 1-6

  • Click on that, and data will be transposed.

Paste Special Method 1-3

  • The even quicker way is following the shortcut method; after copying the data, press the paste special shortcut key ALT + E + S to open paste special method.

Paste Special Method 1-4

  • From the above window, choose “Transpose” as the “Operation.”

Paste Special Method 1-5

  • Click on “Ok,” and data will be transposed instantly.

Paste Special Method 1-6

In order to select the “Transpose” option in Paste Special window, press “E” as the shortcut key, and it will immediately choose the “Transpose” option.

Things to Remember

  • TRANSPOSE function is directly linked to cells, so any changes will reflect in transposed data as well.
  • Paste Special method is not cell linked, so we will not get any updated results in the transposed range.
  • With paste special, we get exact formatting as in the main table, but with the TRANSPOSE formula, we will not get any formatting.

Recommended Articles

This has been a guide to Excel Paste Transpose. Here we learn two ways to paste transposing the data into excel 1) TRANSPOSE function, 2) paste special method, and downloadable excel templates. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>