MrExcel Publishing
Your One Stop for Excel Tips & Solutions

help with formulas


Posted by Jan Mangin on October 29, 2001 7:01 PM

Can someone explain to me in SIMPLE english the difference between relative and absolute cell references and just when you would use them. I am having a very hard time understanding this.
Thank you so much


Posted by Barrie Davidson on October 29, 2001 7:15 PM

Jan, absolute cell references mean that the cell references won't change if you copy the formula somewhere else. Relative cell references will. Try this demonstration. In cell A1 put a value (any value). In cell B1 put this formula
=$A$1
in cell C1 put this formula
=A1

Copy B1 and C1 to B2 and C2 respectively. You'll see that cell B2 has the same formula (=$A$1), but cell C2 will now have this formula:
=A2
note that the cell's reference moved with the location. If you copied cell C1 to cell D1, the formula in D1 would be:
=B1

Does this help you?

BarrieBarrie Davidson

Posted by Dan on October 29, 2001 7:16 PM

Kind of hard to put in to simple english, but here goes.

If you are typing a single formula, then it may not matter what you use. The issue comes in when you try to copy your formula and put it in a different cell. Let's say you have a 2 columns of numbers. In the third column you want to sum the first two columns. You would use relative cell reference. You would write the formula "=A1+B1" in cell C1, for instance. Then you can just copy the formula from C1, paste it in C2 and the formula would automatically change to "=A2+B2".

But if you want to copy a formula in to a different cell and you don't want the original formula changed, then you would use absolute references: "=$A$1+$B$1". You can also use combinations if you parts of the formula to change "=$A2+$B2" changes the row, but keeps the columns the same.

Does that make sense? Also check the Help section in Excel for another explanation.
HTH