Excel Cell References Absolute VS Relative

Absolute Value

Absolute Value: when a cell value remains the same no matter if it is copied and pasted, moved, or has been filled down or over.  An example of an Absolute Value $A$1 + $A$2 when copied to column B will show as $A$1 + $A$2

There are two ways to create an Absolute Value, One have the cursor by the value that you want to be the Absolute (either before the letter, between the letter and the number, or directly after the number of the field address) and use the F4 key on the function key row.  Two manually insert the $ symbol as you are typing the cell reference in. 

Relative Value

Relative Value: when the value of the starting cell changes from row to row or column to column.  An example of a Relative Value A1+A2, when copied to column B will show as B1 + B2

Why do we need to know about Cell References?

Suppose that you need to have a spreadsheet that has a single value that is used for many different calculations and that this value changes often,  If you do not wish to go into the formula and change it all the time and then remember to go into every formula and make the change, then the Absolute Cell Reference will assist us.

In the examples it may not be a problem to change every Formula, however, if we have 100 or 200 Formulas to change imagine how easy it may be to forget to change one or use the incorrect value.

In these examples we see that there are two different results with the same information, one has Relative Cell Reference and the other has Absolute Cell Reference.

 Relative Reference

Absolute Reference

See where we get a VALUE error in the Relative reference sample that is caused by the formula attempting to calculate a number and text.  See Example of Relative Formula.  In the Absolute Reference we can see the formula has worked as expected, see Example Absolute Formula.

Relative Formula

 

Absolute Formula

See how in the Absolute Formula the Dollar signs locked the cell to F3.  Now I only have to change one value and everything else will recalculate by itself.

Hint

We can toggle between the Values and the Formulas / Functions by using CTRL+` (Normally the ` symbol is located above the TAB key).