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.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_001-1024x447.png)
However, everything in the second table is a formula, so if I try and copy and paste this transpose
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_002-1024x794.png)
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.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_003-1024x531.png)
To paste transpose with formulas, firstly, copy the whole table, and right click and select Paste Special…
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_004-1024x601.png)
Next, click on Paste Link
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_005-1024x831.png)
This pastes the whole table as formulas linking back to the original table.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_006-1024x350.png)
Then, select the new table and go to Find & Select and Replace.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_007-1024x901.png)
Replace the = with £= and click Replace All
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_008-1024x567.png)
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.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_009-1024x444.png)
Because all the formulas are now text, this time they don’t change when the table is transpose.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_010-1024x170.png)
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.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_011-1024x344.png)
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.
![](https://keatraining.com/wp-content/uploads/2023/12/032_paste_transpose_012-1024x409.png)