Paste Transpose in Excel
In excel there comes 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.
So, now we will see how we can transpose by using different methods.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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.
#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.
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.
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 a current number of rows data will come to 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.
- Look at the above picture we have selected five columns and two rows (D1 to H2). Now open TRANSPOSE function in D1 cell.
- Choose the data range as the current table from A1 to B5.
- TRANSPOSE is an array formula in excel so we need to close the formula by holding “Ctrl + Shift + Enter” together.
There you go we have got the transpose of our data.
#2 – Paste Special Method
If you have used paste special method then you know how good that tool is. Using Paste Method we can transpose the data quickly, in fact, quicker than the above TRANSPOSE method.
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.
- Now right click and hover on “Paste Special” to see paste special options.
- From the above options choose the method as “Transpose”.
- Click on that and data will be transposed.
- 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.
- From the above window choose “Transpose” as the “Operation”.
- Click on “Ok” and data will be transposed instantly.
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 transposed range.
- With paste special, we get exact formatting as in the main table but with TRANSPOSE formula we will not get any formatting.
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 –