Select the transposed cells and again open Find and Replace ( Ctrl +H), this time under Find what type 1=, and under Replace with type =.Click on the cell where you would like to transpose them and right click, then click on Paste special > Transpose to paste transposed cells.Now, Select the newly modified cells again and Copy them using Ctrl + C.Then hit Replace all and Close the window. A window will pop up, there under Find what type =, and under Replace with type 1=.

First, Select the cells you want to transpose, and click on Find and Replace button on the Home tab (You can also hit Ctrl + H to trigger the Find and Replace function).To transpose and keep formula references in Excel just follow these 5 Simple Steps: How to transpose excel data and keep reference in using Find And Replace! When using the second method we will Paste transpose and keep the values referenced from the original cells. In the first method, we are going to Paste as transpose and keep formula references using Find and Replace functionality. In this post, we will show you, two easy methods on how to paste as transpose and keep formula/value references. But if there are formulas in the cells, the reference will change and mess up our desired values. In Excel, the paste as transpose is extremely useful for transposing a column to a row or vice versa.

Excel formula to transpose data