Paste Link Transpose in Excel
Perform Paste Link and Transpose at the Same Time Using QI Macros Add-in
Excel provides a paste link and a paste transpose function, but it won't do both at the same time. The QI Macros Paste Link Transpose function takes any size table and paste links it into a new sheet.
NOTE: This functionality uses formulas to prevent putting zeros instead of blanks in linked cells.
Here is how the Paste Link Transpose function works:
Just select your original data (6X6):
Then, select the QI Macros menu, click on the "Restacking" drop-down and then "Paste Link Transpose":
QI Macros will transpose your data and paste and link it into a new sheet in your workbook. Here is an example of the above data transposed in a new sheet. If you click on any cell (e.g., C2) you will see the formulas linking that cell to the original data sheet: