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

Cell Reference

Type of Reference

Meaning

$B$4

Absolute cell reference

Both column and row references remain the same when you

copy this cell, because the cell references are absolute.

B$4

Mixed reference

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

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.

$B4

Mixed reference

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.

B4

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.