How to cut and paste transpose in Excel

A common problem when transposing data in Excel is that data is populated in their relative cells. This means that you may occasionally see messed up references when using the Paste function with transpose. In this article, we are going to show you how to cut and paste transpose in Excel.

Download Workbook

Convert formulas into text

The first step of cutting and pasting transpose is to convert formulas into text. This step is essential to maintain cell references without updating them. If there are relative references in your formulas, Excel updates them after copy-paste action based on their new location. Although, this feature is very handful in regular copying and pasting, it is certainly not for transposing.

To convert formulas into text:

1. Select the range you want to cut and paste transpose
2. Press Ctrl + H to open Find & Replace dialog
3. Fill the boxes:
   1. Find what: =
   2. Replace with: &=
4. Press Replace All

After these steps you will see the formulas start with “&” instead of values.

Copy and Paste Transpose

You need to “copy” the data first. As we have mentioned before, Excel doesn’t support Paste Transpose or Paste Special features after cutting. However, this is the simplest way to transpose. Alternatively, you can also try to use TRANSPOSE function. But that approach will become much more complicated.

1. Select your data with “text”-formulas
2. Copy
3. Select the cell/range you want to move your data
4. Either use Paste Special dialog (Ctrl + Alt + V) or Transpose icon in the context (right-click) menu
5. (Optional for Paste Special) Select All under Paste section and mark Transpose. Click OK to finish the task.

Transpose icon in context menu:

Paste Special:

Revive the formulas back

The next step of cut and paste transpose is to convert the strings back to the formulas. All you need to is to reverse the action on transposed data.

1. Select the transposed data with “text” formulas
2. Open Find & Replace again
3. Fill the boxes reversely:
   1. Find what: &=
   2. Replace with: =
4. Press Replace All
   

Final Step

The final step of how to cut and paste transpose in Excel article is to delete the cells you've copied, using “cut”. Otherwise, this doesn’t really count as a “cut and paste” process!