Microsoft Office Tutorials and References

In Depth Information

**Session 2.1**

Using relative and absolute references

Figure 2-6

Formulas Using Relative References

When the formula is copied, the relative reference to the cell (B8) is shifted down and now points to an incorrect cell (B9).

Formulas Using Absolute References

When the formula is copied, the absolute reference to the cell ($B$8) continues to point to that cell.

Another type of reference supported by Excel is the mixed reference. A
mixed reference

contains both relative and absolute cell references. A mixed reference for cell B8 is either

$B8 or B$8. In the case of the mixed reference $B8, the column portion of the reference

remains fixed, but the row number adjusts as the formula is copied to a new location. In

the B$8 reference, the row number remains fixed, whereas the column portion adjusts to

each new cell location.

As you enter a formula that requires an absolute reference or a mixed reference, you can

type the dollar sign for the column and row references as needed. If you have already

entered a formula and need to change the type of cell reference used, you can switch to edit

mode and then press the
F4
key. As you press this function key, Excel cycles through the dif-

ferent references for the cell in the formula at the location of the insertion point. Pressing the

F4 key changes a relative reference to an absolute reference, then to a mixed reference for

the row, then to a mixed reference for the column, and then back to a relative reference.

In Amanda’s family budget, monthly expenses vary greatly throughout the year. For exam-

ple, tuition is a major expense, and that bill must be paid once in January and once in

August. Amanda knows that the family has more entertainment and miscellaneous expenses

during the month of December than at other times. The family’s monthly income also fluctu-

ates as Joseph brings in more income during the summer months than at other times.

Amanda would like her budget worksheet to keep a running total of the family’s net income

as it progresses through the year. For example, she knows that the family will start the year

with less money because of the tuition bill in January. Amanda wonders how many months

pass before they recover from that major expense and begin saving money again.

One way to calculate the running total is to add the net income values of consecutive

months. For example, to figure out how much money the family has saved or lost after

two months, you add the net income for January to the net income for February, using the

formula
=SUM(B24:C24)
. To figure out the total net income for the first three months, you

use the formula
=SUM(B24:D24)
; through the first four months the formula will be

=SUM(B24:E24)
, and so on.