Reference Questions and Answers

Start Your Free Trial

In Excel, what is the difference between "relative addressing" and "absolute addressing"? When might "absolute addressing" be necessary?

Expert Answers info

Jonathan Beutlich, M.A. eNotes educator | Certified Educator

briefcaseTeacher (K-12), Professional Writer

bookB.A. from Calvin University

bookM.A. from Dordt University

calendarEducator since 2014

write6,198 answers

starTop subjects are Literature, Science, and History

The relative addressing and absolute addressing (relative referencing and absolute referencing if you are using Google Sheets) is something that a user needs to pay attention to if and when a formula is being "dragged" (copied) into more cells. The relative addressing will move the rows and/or columns along with the formula while an absolute addressing will always keep the cell being referred to the same.

For example, a coach of a cross country team might have names in column "A," opening miles in column "B," and average miles in column "C." The coach might then want to know what the difference between the opening mile and average mile is for each athlete. In column "D," the coach would write a formula that says to subtract B2 from C2. This would be average mile minus opening mile for the first athlete. That formula can be copied into every row below it, but the coach wants the formula to drag down with each athlete. Athlete 2 needs to have the formula say C3-B3. The reference cells are relative as they move down with each athlete. The absolute addressing might be used in the same setup. The coach might desire that each athlete reduces his/her average mile pace by 2%. The user could place 2% in an unused cell. A formula could be written that says to take an athlete's average mile pace minus the cell that says 2%. This cell needs to be an absolute cell reference, so that the reference never changes no matter where the formula is applied. This is done by adding the "$" in front of the row and column info in the formula. As the formula is dragged down and copied, the 2% will always be applied.

Further Reading:

check Approved by eNotes Editorial

owls21 eNotes educator | Certified Educator

calendarEducator since 2016

write17 answers

starTop subjects are Literature and History

When a user copies a formula in an Excel spreadsheet and the cell references change in response, the process is called "relative addressing."

When a user copies a formula into another cell and the cell reference does not change, the process is called "absolute addressing."

Relative addressing (the most commonly used) is particularly good for totaling column or row numbers in a table, whereas absolute addressing might be necessary when the user needs entries in a given row and/or column to remain unaffected. Note: in order to make references absolute, the addition of a dollar sign ($) to the address is necessary. Absolute addressing is useful when calculating sales tax, projected sales, or gross pay for multiple employees with an hourly wage.