

When you copy and paste cells in Excel, you can either paste them as links or transpose them. Excel doesn’t allow doing both at the same time. Unfortunately, you often need to link and transpose. But there are three ways for accomplishing this: Doing it manually, using the array formula {=TRANSPOSE()} or Professor Excel Tools.
Contents
When you set the tick at “Transpose”, the “Paste Link” button is greyed out.
You’ve copied a range of cells. Now you want to paste them. But instead of simply pasting them you also want to paste links and transpose (exchange rows and columns) them.
The problem: As soon as you set the tick at “Tranpose”, the “Paste Link” is greyed out.
Workaround: Use the OFFSET formula for pasting links and tranpose them at the same time
The OFFSET formula is powerful but unfortunately especially for beginners not very easy to use. Roughly said, you define a base cell and tell Excel which value counting from the base cell to return.
First: Prepare the number 0-2 (the maximum number of rows – 1) on the top and 0 to 1 on the left hand side. We use this as a reference. There are certainly more elegant ways (e.g. with ROW() or COLUMN()) but this is the fastest and easiest method. The following number match to the picture above:
The formula in the example above looks like this:
=OFFSET($B$2,F$1,$E2)
Steps for pasting as links and transposing in Excel at the same time
With an array formula you don’t have to drag and drop cells manually. Despite the advantage of not having to do anything manually, this way is slightly more complicated. Furthermore it comes with the disadvantages of all the array formulas: Their size is not (easily) changeable. So if the cell range of your source data changes, you usually have to set up the array formula again.
Follow these steps for setting a the array formula for pasting cells linked to their sources and transposing them (the numbers are corresponding with the picture on the right hand side):
=TRANSPOSE('Original Range')
and press Ctrl
Let’s take a look at the obvious method: Doing it manually. If your cell range is not too large it’s often the fastest solution. Of course, doing things manually is a source for possible errors.
Professor Excel Tools: Paste as Link and Tranpose with 2 clicks
As all the previous methods have some major disadvantages, pasting as link and transposing data was one of the first functions we’ve included in our Excel add-in:
This function works across workbook as long as source and target workbooks are open at the same time.
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 4
Shozib Javaid
Hi
Sir, Thanks for sharing useful information. I have a question could you please help me.?
The formula which I am using is =IF(MOD(ROW()-1,4)=0,( TRANSPOSE(A4149:A4151) ),””) , just one thing more, If you please help, The rows which are to be copied in to columns are mostly hyperlinks, but when I applied this formula, it worked perfectly (converted rows to columns with desired interval) but did not copy the hyperlinks as well. How can I tackle this I want hyperlinks to be transposed also.
Waiting for your response.
Thanks
Shozib
Bill Ritzel
I have a chart pointing to a horizintal row of cells which are linked to a vertical column on another sheet. Each horizontal cell contains the same thing, something like =TRANSPOSE(‘Monthly Data’!$W$3:$W$15)
I can’t seem to find a combination in the paste special menu to both link and transform the data. Any ideas would be appreciated Thanks in advance.
Curtis
With the empty cells selected , type this formula: =TRANSPOSE(A1:G6) Since our formula needs to be applied to multiple cells , press Ctrl Shift Enter to make it an …
Claude
I DL’s the trial but when I try to transpose I get an error box stating:
“You are trying to link or copy from a workbook which is not open or have renamed the workbook. If you have renamed your workbook, please copy the original cells again.”
I have a simple 1 sheet workbook open with only 3 lines that I wish to transpose and link data.
What am I doing wrong?