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
in cell C1 put this formula

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:
note that the cell's reference moved with the location. If you copied cell C1 to cell D1, the formula in D1 would be:

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.