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
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 doesn’t change.
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).
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.
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.
You can also manually type in the currency symbol [shift] 4 in the appropriate location.
Excel Names are another option when Absolute References are necessary.
As always, we’re available to help you with your individual questions. We’ve offered classes and training in the Reno area for over twenty years. Why? Because we’re dedicated to helping people understand. So please reach out. You can contact us via email, text, or voicemail to get your training scheduled. Diana also trains for Washoe County and UNR Extended Studies.