michigan informatics

Absolute References

In absolute references, a cell or range of cells is designated to remain constant within the function regardless of the location of the formula.  This is accomplished by adding the prefix of “$” before the column letter and the row number.

For example, C9=C8/B8*100 [percentage—part over whole times 100].  To copy this formula into cell D9 and maintain the relationship between the service provided (cells C8 or D8 or E8) and the total served (cell B8) within the state, the formula needs to be stated as: C9=C8/$B$8*100.  The ‘$’ tells Excel to look for a specific cell to use within the formula versus relying on a relative relationship. 

Absolute cell references work well when a specific cell is needed to finalize a calculation.  Copying the formula C9=C8/$B$8*100 to a D10 maintains the specific (absolute) cell reference to B8, while incorporating the relative references for the remainder of the formula.  The resulting formula would be:  D10=D8/$B$8*100.