Copying Formulas and Pasting Them in a Transposed Direction Without Changing the Relative References

by  About
       

To paste copied cells in a transposed direction:

1. Select a range of cells in the sheet, and press Ctrl+C.
2. Select any cell in the sheet, right-click, and then select Paste Special from the shortcut menu.
3. In the Paste Special dialog box, select Transpose, and then click OK.

The Transpose option pastes the data in the opposite direction (that is, horizontally to vertically, or vice versa).

The Paste Special dialog box has a Paste Link option that enables to creating links to the source data while pasting copied cells in the new location.

Problem:

You cannot select the Transpose option together with the Paste Link option. In other words, you cannot create links while changing the direction using Paste Special.

Solution:

Use the TRANSPOSE function together with the Array Formula technique to create links that change direction.
1. Select the range A2:B6, which has the range Name Data defined for it. The size of the range is 5Rx2C, that is, four rows by two columns. (To quickly find the measurement, select the range and watch for the size in the Name box while pressing the mouse button.)
2. Select a range starting from cell A8 whose size is 2Rx5C, that is, the same size in the opposite direction.
3. Type the formula =TRANSPOSE, and then press Ctrl+A.
4. Press F3, paste the Name Data, and then press Ctrl+Shift+Enter Screenshot // Copying Formulas and Pasting Them in a Transposed Direction Without Changing the Relative References
Copying Formulas and Pasting Them in a Transposed Direction Without Changing the Relative ReferencesCopying Formulas and Pasting Them in a Transposed Direction Without Changing the Relative References



4 thoughts on “Copying Formulas and Pasting Them in a Transposed Direction Without Changing the Relative References

  1. “make this:

    first:
    cell a1: value 2000
    cell b1: value 2001
    cell c1 :value 2002

    cell a2: formula: ‘=A$1′
    cell b2: formula: ‘=B$1′
    cell c2: formula: ‘=C$1′

    now select range A2:C2
    Copy

    select a5

    past special formula transpose

    done!”

  2. “If you like copy& paste transpose formula from one worksheet to another , first method works. Second method is better, but if you simply copy&paste special with transpose u have to include worksheet-name.

    make this:

    first: in worksheet with name ‘source’
    cell a1: value 2000
    cell b1: value 2001
    cell c1 :value 2002

    cell a2: formula: ‘=source!A$1′
    cell b2: formula: ‘=source!B$1′
    cell c2: formula: ‘=source!C$1′

    now select range A2:C2
    Copy

    select a5

    past special formula transpose

    done! “

  3. “make this:

    first:
    cell a1: value 2000
    cell b1: value 2001
    cell c1 :value 2002

    cell a2: formula: ‘=A$1′
    cell b2: formula: ‘=B$1′
    cell c2: formula: ‘=C$1′

    now select range A2:C2
    Copy

    select a5

    past special formula transpose

    done!”

  4. “If you like copy& paste transpose formula from one worksheet to another , first method works. Second method is better, but if you simply copy&paste special with transpose u have to include worksheet-name.

    make this:

    first: in worksheet with name ‘source’
    cell a1: value 2000
    cell b1: value 2001
    cell c1 :value 2002

    cell a2: formula: ‘=source!A$1′
    cell b2: formula: ‘=source!B$1′
    cell c2: formula: ‘=source!C$1′

    now select range A2:C2
    Copy

    select a5

    past special formula transpose

    done! “

Leave a Reply

Your email address will not be published. Required fields are marked *


5 − = one

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>