Microsoft Office Tutorials and References
In Depth Information
Using relative and absolute references
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.