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...
[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.
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
Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another. For example, if I’m creating a personal cash flow worksheet and at the end of the month I
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
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.