Now the formulas have been transposed and keep references. Click Replace All > OK > Close to finish operation and close dialogs. Press Ctrl + H to enable Find and Replace dialog again, enter #= and = into Find what and Replace with textbox separately. Then press Ctrl + C to copy the selected cells, and select a cell to right click to open the context menu, and click Paste Special > Transpose to paste the cells in transposition.ĥ.

Click Replace All, a dialog pops out to remind you the number of replacements, click OK > Close to close dialogs.Ĥ.

In the Find and Replace dialog, type = into Find what textbox, #= into Replace with textbox. Select the cells you want to transpose, and press Ctrl + H to display Find and Replace dialog.Ģ.

To transpose and keep reference in Excel, you can apply the Find and Replace function.ġ. Transpose and keep reference with Kutools for Excel Transpose and keep reference with Find and Replace function In this article, I introduce how to paste transposed and keep formula reference easily. In Excel, the paste as transpose is useful when you want to transpose a column to a row, but if there are some formulas in the cells, while you paste them as transpose, the references will change as below screenshot shown. How to paste transposed and keep formula reference in Excel?

Excel formula to transpose data