How to transpose and link values in Excel?
In Excel, you can paste a range data as link, and also can paste values in transpose order. But do you know how to transpose the pasted values and link them as well? In this article, you may find the answer.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
To transpose and link the pasted values, you need to name the copied values first and then apply a formula.
1. Select the values you want to transpose and link, and go to Name box to give it a name.
2. Then select a range that you want to place the transposed and linked pasted values, and type this formula =TRANSPOSE(SPORT) (SPORT is the range name you have specified in step 1), press Shift + Ctrl + Enter keys to get the result.
Now when you change the original data, the pasted data will be change as well.
You also can apply Find and Replace function to transpose and link the values.
1. Select the original values, press Ctrl + C to copy them, and select a cell and right click to select Link from Paste Special sub menu. See screenshot:
2. Then press Ctrl + H to enable the Find and Replace dialog, and in the Find what textbox, enter =, in the Replace with box, type #=. See screenshot:
3. Click Replace All, a dialog pops out to remind you the number of replacements. Click OK > Close to close the dialogs.
4. And then press Ctrl + C to copy the replaced values, and select a cell and right click to select Transpose option in the Paste Special sub menu from the context menu. See screenshot:
5. Press Ctrl + H to enable Find and Replace dialog again, and enter #= and = into Find what and Replace with textbox separately. See screenshot:
6. Click Replace All, and a dialog pops out to remind you the number of replacements, click OK > Close to close the dialogs. Now the values have been paste transposed and linked.