Microsoft Office Tutorials and References
In Depth Information
Absolute versus Relative Addressing
Absolute versus Relative Addressing
Your Age in Days
How many days have you
been alive? Enter today’s
date (e.g., 12/5/2012) in
cell A1. Next, enter your
birth date (e.g., 6/22/1996)
in cell A2. Select cell A3
and enter the formula =
A1–A2. Format cell A3 to
the General style using the
Format Cells dialog box,
and cell A3 will display
your age in days.
The next sections describe the formulas and functions needed to complete the calculations
in the worksheet.
As you learned in Chapters 1 and 2, Excel modii es cell references when copying
formulas. While copying formulas, however, sometimes you do not want Excel to change
cell references. To keep a cell reference constant when copying a formula or function, Excel
uses a technique called absolute cell referencing. To specify an absolute cell reference in a
formula, enter a dollar sign ($) before any column letters or row numbers you want to keep
constant in formulas you plan to copy. For example, $B$4 is an absolute cell reference,
whereas B4 is a relative cell reference. Both reference the same cell. The difference
becomes apparent when they are copied to a destination area. A formula using the absolute
cell reference $B$4 instructs Excel to keep the cell reference B4 constant (absolute) in the
formula as it copies it to the destination area. A formula using the relative cell reference
B4 instructs Excel to adjust the cell reference as it copies it to the destination area. A cell
reference with only one dollar sign before either the column or the row is called a mixed
cell reference . When planning formulas, be aware of when you need to use absolute,
relative, and mixed cell references. Table 3 – 6 gives some additional examples of each of
these types of cell references.
Table 3 – 6 Examples of Absolute, Relative, and Mixed Cell References
Type of Reference
Absolute cell reference
Both column and row references remain the same when you
copy this cell, because the cell references are absolute.
This cell reference is mixed. The column reference changes when
you copy this cell to another column because it is relative. The
row reference does not change because it is absolute.
Absolute referencing is
one of the more difi cult
worksheet concepts to
understand. One point to
keep in mind is that the
paste operation is the only
operation affected by an
absolute cell reference.
An absolute cell reference
instructs the paste
operation to keep the
same cell reference as it
copies a formula from one
cell to another.
This cell reference is mixed. The column reference does not
change because it is absolute. The row reference changes when
you copy this cell reference to another row because it is relative.
Relative cell reference
Both column and row references are relative. When copied to
another cell, both the column and row in the cell reference are
adjusted to rel ect the new location.
The next step is to enter the formulas that calculate the following values for July:
cost of goods sold (cell B14), gross margin (cell B15), expenses (range B18:B22), total
expenses (cell B23), and the operating income (cell B25). The formulas are based on the
projected monthly revenue in cell B13 and the assumptions in the range B2:B8.
To Enter a Formula Containing Absolute Cell References
The formulas for each column (month) are the same, except for the reference to the projected monthly revenue
in row 13, which varies according to the month (B13 for July, C13 for August, and so on). Thus, the formulas for
July can be entered in column B and then copied to columns C through G. Table 3 – 7 shows the formulas for deter-
mining the July cost of goods sold, gross margin, expenses, total expenses, and operating income in column B.