Using Absolute References
The Fill Handle is Awesome
Using the Fill Handle to copy formulas down (or across) a list of data is an incredible time saving feature. However, problems arise when you want to reference a specific cell over and over again.
The first formula works great. 10% x $10 results in a $1 discount. You can see the resulting problems after the formula gets copied down. The second and third (and following values if the list were longer) do not work.
The cell reference for the 10% discount changes as you copy the formula down. The formula for the second result multiplies “nothing” x $20, resulting in a $0 discount. The third result displays a #VALUE! error, because the copied formula multiplies text “Actual Price” x $30.
If the list were longer, the fourth formula would result in $9 x $30, for a $270 discount.
Absolute References are the Solution
In the first formula, LOCK the cell reference to the 10% discount using Absolute Reference, leaving the cell reference for the price just as it is.
- A dollar symbol ($) before both the column and row reference locks the reference to the one specific cell [allowing you to copy the formula left, right, up or down and have the cell reference locked to the cell]
- A dollar symbol ($) before the row reference locks the row [allowing you to copy the formula up or down and have the cell reference locked to the specific row]
- A dollar symbol ($) before the column reference locks the column [allowing you to copy the formula left or right and have the cell reference locked to the specific column]
Rather than type the dollar symbol, you can press the [F4] key to change which reference gets locked.
The result:
When each formula is in edit mode, you see the cell reference for $D$3 stays consistent, even when the reference for B6 is copied to B7 and B8.
- =$D$3*C6
- 10% of $10 = $1 discount
- =$D$3*C7
- 10% of $20 = $2 discount
- =$D$3*C8
- 10% of $30 = $3 discount
There are other posts about using Excel, including posts on absolute vs relative references, using proper Order of Operations, and grouping dates in PivotTables. Check out what you can do with VLOOKUP or the newest XLOOKUP function.
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.
