Can you explain the difference between Absolute and Relative Referencing?!

Dear Dawn

Mandy asked teh same question so I've just copied and pasted the answer and I hope this clarifies things!!

It was a pleasure having you in the Excel Intro training today. Thank you for your kind remarks!!

Dollar signs are required when you are trying to copy and paste a formula and you do not want teh cells to change relatively!!

As you might recall from the class when we calculate, e.g. AutoSum and want that to be copy and paste it in the other cells which may be below or on the right hand side you normally use the Fill Handle (the dark area at the bottom right corner of the cell) and drag that across or down depending on the layout of the data. And therefore we get the result that we are expecting. This type of copying and pasting where the cells change automatically is known as relative referencing!!

But sometimes we don't want certain cells to change automatically when we copy and paste the formulas. This could be for example if you have VAT and you want all the cells which have sales amount to multiply with the cell that has got VAT percentage. In this case you might not want to fix the cell containing the VAT % so this is where you use the Dollar ($) sign before the letter and before the number e.g. $b$6. This is known as Absolute referencing.

Another good example of absolute could be all the salesperson getting a specified % of commission.

TIP: You can use the F4 key on teh keyboard when you are originally putting the formula.


I hope this clarifies and if not please don't hesitate to ask further clarification!!