Results 1 to 6 of 6

Copy/Paste exact formula

This is a discussion on Copy/Paste exact formula within the Excel Questions forums, part of the Question Forums category; How do you copy and paste a formula over an entire column without Excel changing the formula for each cell?...

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    15

    Default

    How do you copy and paste a formula over an entire column without Excel changing the formula for each cell?

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    India
    Posts
    627

    Default


  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    India
    Posts
    627

    Default

    Sorry for the blank reply

    Here goes:

    1. Select the cell containing the formula to be copied
    2. Hit F2 to enter Edit mode
    3. Place an apostrophe (‘) before the ‘=’ sign to make it a string
    4. Hit Enter
    5. Do ^c
    6. Select the col. in which you want to paste the formula
    7. Hit ^v
    8. Keeping the column selected, do Edit>Replace
    9. In the dialog box type ‘= in the ‘Replace’ box
    10. Type = in the Replace with box.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Mala ! You're the Mala I "know", right ? nice to see you around here.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Leicester, UK
    Posts
    40

    Default

    Hi

    You could also use absolute referencing. Just place a $ sign before the column and / or row parts of cell references in the formula that you don't want to change when copied. i.e.

    =Sum(A1:B6) becomes =Sum($A$1:$B$6)

    If you press F2 to edit your formula, you can then use F4 to toggle through the options (A1, $A$1, $A1, A$1) when the cursor is over a cell reference in your formula.

    If you play around with this a little, you'll see how it works.

    JayKay

    [ This Message was edited by: JayKay on 2002-06-13 10:17 ]

  6. #6
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    722

    Default

    Use dollar signs! =(A1)+(B1) will become = (A2)+(B2) when copied to the next cell down. =($A$1)+($B$1) will stay =($A$1)+($B$1) no matter where you copy it.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com