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
In a similar scenario, let’s look at the address for the White House, 1600 Pennsylvania Ave. It won’t matter where you live, the White House address would stay the same. Regardless of where you live, the address of the White House 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).
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.
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. You might want to look at another post about Absolute References with a couple of videos.
You might also be interested in using posts about Order of Operations, or grouping dates in PivotTables.
As always, we’re available to help you with your individual questions. With over twenty years of experience, we are proud to have clients in many industries including: Real Estate, construction, manufacturing, non-profits, and research and development.
You can reach us by calling or texting 775 . 525 . 0885 or emailing info [at] MyRenoComputerTutor [dot] com
We’re dedicated to helping people understand what they’re doing. So please reach out. You can contact us via email, or voicemail, and you can even text us to get your training scheduled. Diana also trains for Washoe County, City of Reno and UNR Extended Studies.
