April 30


Excel – Paste a link and Paste Special transpose your data

By Sharyn Baines

April 30, 2015

Modify, Paste, VLOOKUP

Without a doubt ‘Paste Link’ is one of the best time-saving features in Excel.  Linking information from one cell to another saves you from having to update data in several places.

Another fabulous time-saving feature is ‘Paste Special Transpose’. This allows you to copy a horizontal range and paste it vertically, or vice versa. An absolute gem.

But have you ever tried to use the two together?  It isn’t easy at all.  In this post I want to share a wee work-around that will allow you to use the Paste Link and Paste Special Transpose features together.

Pasting a link to the target figures

In the example below you will see that I have open a ‘Monthly Targets’ worksheet which holds the sales targets for our sales team. The monthly targets for each sales person are recorded horizontally under the relevant month heading. This worksheet is maintained by the Regional Sales Manager and it updated often.


I have created the other worksheet, ‘Sales by Month – Actual vs Target’, in order to analyze if the sales team have met their targets. To ensure the ‘Target’ rows always match the targets as set by the Regional Sales Manager I’ve decided to link the data from his workbook into mine.

However, as you will see, the layout of my worksheet differs from his. Where his has the sales person’s name vertically listed in column A, mine has them listed horizontally in row 3.  So what I need to do is a Paste Link and Paste, Transpose so that the data is linked AND moves from a vertical list to a horizontal one.

The first step is to select and then copy the data I want to link into my analysis workbook.


Now I will use the Paste Link option to paste a link to the data into my analysis workbook. Please note how I have moved the paste area away from my table and dropped it into some empty cells. This is just to keep the data away from where I will be copying it later. I will delete this range once I have completed the task.


With the linked range still selected pressing CTRL + H displays the Replace dialog box. In the Find what box I’ll type = and in the Replace with box type &=. Click Replace All. Excel will whip through the selected range and place the ‘&’ character in front of the = for each formula. This temporarily turns the formula into text.


Using Paste Transpose to change the list from vertical to horizontal

Now I will copy the same range and then use the Paste Transpose option to paste the data into the ‘Target’ row for April.


Linking the transposed data

With the target data still selected pressing CTRL + H will display the Replace dialog box once again. I’ll now change the range from text back to a formula. In the Find what box type &= and in the Replace with box type = then click Replace All.


The range will once again become a formula and the data is now linked and transposed.

Use VLOOKUP to link and transpose data

Another way to link and transpose data is to use the VLOOKUP function.


If you aren’t familiar with this function you would most likely enjoy my blog ‘Excel – Using VLOOKUP to compare two lists’.

If you enjoyed this you may also enjoy...

Remove Drop Down List in Excel

[Watch on YouTube] / [Subscribe to our YouTube Channel]Need to remove a drop-down list in Excel? In this blog you will learn how to remove the drop-down list as well as how to remove the menu items list.How to remove drop down list in ExcelClick onto the cell that has the drop-down list on it. In

Read More

How to Freeze rows in Excel

[Watch on YouTube] /  [Subscribe to our YouTube Channel]Would you like to keep row headings in Excel when you are scrolling so that you can easily identify your columns?  What if I told you there was a way to freeze rows and columns in Excel, so your headings come with you when you scroll down

Read More

Find, modify and break links to an Excel workbook

Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data

Read More

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!