I have two tables, with compounds along the top and dates down the side and I want to swap around the rows and columns of the second table, so that the compounds go down the side and the dates go along the top.
However, everything in the second table is a formula, so if I try and copy and paste this transpose
The formulas get messed up and I don’t want to paste it as values because then the table won’t update if I change anything in the first table later. I’m guessing you have also tried this and realised that it doesn’t work, which is what led you to this blog post.
To paste transpose with formulas, firstly, copy the whole table, and right click and select Paste Special…
Next, click on Paste Link
This pastes the whole table as formulas linking back to the original table.
Then, select the new table and go to Find & Select and Replace.
Replace the = with £= and click Replace All
This puts a pound symbol at the beginning of every formula in front of the equals sign. This changes all of the formulas into text. It doesn’t have to be a pound symbol, it can be anything, you just need something in front of the equals sign. Now, we can copy the new table and paste it transpose.
Because all the formulas are now text, this time they don’t change when the table is transpose.
Now, we will do the reverse of what we just did. Select the transposed table and use Find and Replace again to replace the £= with = and click Replace All.
This changes the text back into actual formulas, which are now linked to the second table. So we have pasted a table transposed with formulas.
