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