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...
[Watch on YouTube] / [Subscribe to our YouTube Channel]Learn how to edit a drop-down list in Excel. In this blog you will learn how to find, remove and add items to the drop-down list. An extra step is also included for those using older versions of Excel.How to find items in a drop-down listClick onto the
[Watch on YouTube] / [Subscribe to our YouTube Channel] Learn how to create a drop-down list in Excel. In this blog you will learn two different ways to create a drop-down list and the advantages of each as well as an extra step needed for older versions of Excel when creating a drop-down list from another
[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
[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
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
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