Let's
pretend that you need to calculate the prices of items in stock with
two different price discounts. Take a look at the worksheet below.
Examine
the formula in cell E4. By making the first cell reference $C4, you
keep the column from changing when copied across, but allow the row
to change when copying down to accommodate the prices of the
different items going down. By making the last cell reference
A$12, you keep the row number from changing when copied down, but
allow the column to change and reflect discount B when copied
across. Confused? Check out the graphics below and the
cell results.
Copied
Across
Copied
Down
Now,
you might be thinking, why not just use 10% and 15% in the actual
formulas? Wouldn't that be easier? Yes, if you are sure the
discount percentages will never change - which is highly unlikely.
It's more likely that eventually those percentages will need to be
adjusted. By referencing the cells containing 10% and
15% and not the actual numbers, when the percentage changes all you
need to do is change the percentage one time in cell A12 and/or B12
instead of rebuilding all of your formulas. Excel would
automatically update the discount prices to reflect your discount
percentage change.
Summary
of absolute cell reference uses:
$A1
Allows the
row reference to change, but not the column reference.
A$1
Allows the
column reference to change, but not the row reference.
$A$1
Allows
neither the column nor the row reference to change.
There is a shortcut for placing absolute cell
references in your formulas!
When you are typing your formula, after you type a
cell reference - press the F4 key. Excel automatically
makes the cell reference absolute! By continuing to press F4,
Excel will cycle through all of the absolute reference
possibilities. For example, in the first absolute cell
reference formula in this tutorial, =B4*$B$10, I could have typed,
=B4*B10, then pressed the F4 key to change B10 to $B$10.
Continuing to press F4 would have resulted in B$10, then
$B10, and finally B10. Pressing F4 changes only the cell
reference directly to the left of your insertion point.
I hope this tutorial has made these cell reference
types "absolutely" clear!
Notice: You have the author's
permission to use the tutorials on this site for your personal
use only. If you would like to use these tutorials for
training classes or other commercial use, please contact kstille@esmartweb.com.
Distribution in print or on the Web without the author's permission
is strictly prohibited.
Featured Sites
HandofGrace.com
Quality,
affordable sterling silver beaded bracelets & mothers
bracelets. Swarovski crystals, fresh water pearls, Venetian
glass and other unique findings are featured in our bracelets.
The perfect gift for any occasion!
BakedByMom.com
Offering homemade cookie and candy boxes or gifts at reasonable prices, Baked by Mom strives to provide the best quality product in it's class. Try the White Chocolate Chip & Lime cookies, Rocky Road Candy and Caramels - they get rave reviews!
