copy a formula that does not change the column or row number?

  • Thread starter s2m via OfficeKB.com
  • Start date
S

s2m via OfficeKB.com

how can I copy a formula that does not change the column or row number?
I want to use the same formula with set column and row but when I copy from
cell to cell it goes to the next column or row
 
S

SamuelT

If I understand correctly, you want to set the cells as absolut
references

In the formula, click the cell reference and then hit F4

If you had A1, it'll become $A$1. This means this particular cell wil
always be used, wherever you copy the formula. If you hit F4 again
you'll get A$1, which means the row will always be 1, but the colum
can change. Hit it one more time you'll get $A4, which means tha
column A will always be referenced, but you can drag the rows. Hit F
one more time, and you be back to your relative reference of A1

Hope that helps

Samuel
 
T

tim m

In your formula put $ before the Row and column names and when you copy it
will look at that cell rather than progressing it. for example $A$1
 
C

CLR

Use "Absolute" references instead of "Relative" references.

=$A$1
instead of
=A1

Vaya con Dios,
Chuck, CABGx3
 
S

samprince

knowing how to use $ sets you on the road to Excel wisdom.

Once i worked that out it opens up some many doors as you see futher
capabilities.

I only knew you could use F4 to put the $'s in until recently [a week
ago \-: ] up until then I'd been typing them in manually bah!
 
G

Gord Dibben

Change the reference to Absolute.

=A1 is relative and will increment as copied.

=$A$1 is absolute and will remain as is when copying

To add the $ signs, highlight the references in the formula bar and hit F4 key
to cycle through your options.


Gord Dibben MS Excel MVP
 
Top