April 30

0 comments

Excel – Paste a link and Paste Special transpose your data

By Sharyn Baines

April 30, 2015

Modify, Paste, VLOOKUP

person writing on white paper

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’.

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.

If you enjoyed this you may also enjoy...

How to freeze a row in Excel

[Watch on YouTube] /  [Subscribe to our YouTube Channel]Let’s look at how to freeze a row in Excel.  It can be a real pain when you don’t have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns. 

​Read More

Insert an Excel function into your workbook

Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few. 

​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"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"0328f":{"name":"Main Accent","parent":-1},"7f7c0":{"name":"Accent Darker","parent":"0328f","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"0328f":{"val":"var(--tcb-skin-color-0)"},"7f7c0":{"val":"rgb(4, 20, 37)","hsl_parent_dependency":{"h":210,"l":0.08,"s":0.81}}},"gradients":[]},"original":{"colors":{"0328f":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45,"a":1}},"7f7c0":{"val":"rgb(4, 21, 39)","hsl_parent_dependency":{"h":210,"s":0.81,"l":0.08,"a":1}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"0328f":{"name":"Main Accent","parent":-1},"7f7c0":{"name":"Accent Darker","parent":"0328f","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"0328f":{"val":"var(--tcb-skin-color-0)"},"7f7c0":{"val":"rgb(4, 20, 37)","hsl_parent_dependency":{"h":210,"l":0.08,"s":0.81}}},"gradients":[]},"original":{"colors":{"0328f":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45,"a":1}},"7f7c0":{"val":"rgb(4, 21, 39)","hsl_parent_dependency":{"h":210,"s":0.81,"l":0.08,"a":1}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article

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

>