Relative vs Absolute Cell References

Excel, Microsoft Office | Comments Off on Relative vs Absolute Cell References

Cell references

When creating formulas, we use cell references rather than actual values to show Excel which cells to use in the calculation. By referencing which cell to use in a formula. when the value of the cell changes, the result of the formula updates automatically.

By default a cell reference is relative, which allows the formula to be copied and applied to other cells in the worksheet without your having to manually create the formula repeatedly.

Relative vs Absolute

Relative references can be thought of as  neighbors. The neighbor right next door may be Jed. If you move to another house, the neighbor right next door might be Ellen. Who your neighbor is changes, relative to in which house you live. Your address may be 1218 Grand Ave., but if you move, you might live at 614 Park Ave. Your address is relative to where you live. Your next door neighbor lives next door to you.

In the same scenario, let’s say the President is John F. Kennedy. His address is 1600 Pennsylvania Ave. It won’t matter where you live, his address would stay the same. Regardless of where you live, or the value of who is President, the address of the President doesn’t change.

Relative Reference:

A relative cell reference adjusts which cell is referenced according to the location the formula is copied or moved.

In this example, the Sales column is calculated by using the SUM function on cells B6:E6 (all cells from B6 through E6). When the function is copied, the cell reference will update relative to which row the function is pasted. When it is pasted in row 7, the cell reference in the function will be =SUM(B7:E7).relativeref

Absolute Reference:

An absolute cell reference does not change when the formula is moved. You’ll need to use an absolute reference in your function or formula when you don’t want the cell reference to update.

In this example, the Commission Rate is in cell G2. Its location will not change. When that cell is used in a formula or function, the cell reference needs to be set to Absolute. It would look like this $G$2. The currency symbol tells Excel to refer to Column G, Row 2, absolutely every time the formula or function is pasted.absoluteref

The other cell reference used in the formula (F6) is a Relative reference. When this formula gets copied and pasted into row 7, the cell with the relative reference will update to F7, but the cell with the absolute reference will not update. It will refer to cell $G$2 regardless of where in the worksheet the formula is pasted. The result would be =F7*$G$2

Setting Absolute Reference

When creating the formula, have the cursor active in the cell reference that needs to be Absolute, and press the [F4] key on your keyboard. This will activate the currency symbol ($) for both the column and row reference. Repeatedly pressing the [F4] key will toggle the Absolute reference off the row, or off the column, or off for both.mixed

You can also manually type in the currency symbol [shift]4 in the appropriate location.

Contact us and ask about personalized training in Excel. We also have posts on using proper Order of Operations  grouping dates in PivotTables.