[vip_students] Excel Tutorial:Lesson 28,Using Absolute and Relative Cell References

Using Absolute and Relative Cell References

Note: This email contains an attachment which is an excel document.


Introduction.  In lesson one, you learned that a cell reference is the
column and row coordinates for a cell. You have also learned that you can
use cell references in formulas.

Excel distinguishes between two types of cell references: absolute cell
references and relative cell references. So far in this tutorial you have
only used relative cell references.


Relative Cell References.  When you use a relative cell reference in a
formula, Excel finds the data referenced by using the cell with the formula
as a starting point. For example, suppose cell B6 contains the formula
=A5*6. Excel will find the value to multiply by 6 by looking one cell above
and one cell to the left of B6. In another example of relative cell
referencing, cell D24 contains the formula =F27/4. In this instance, Excel
will find the value to divide by 4 by looking three cells down and two cells
to the right.

In a formula that contains a relative cell reference, the reference will
automatically change if the position of the cell containing the formula
changes. For example, you copy the formula in cell B6 to cell G7. Excel will
automatically change the formula to =F6*6. The cell reference becomes F6
because it is one cell above and one cell to the left of cell G7.

Absolute Cell References.  When you use an absolute cell reference, Excel
finds the data reference using the exact coordinates of a cell - regardless
of the position of the cell that contains the formula. An absolute reference
is formatted as $A$1, $B$1, $C$1, and so on. For example, cell B6 contains
the formula =$A$5*6. Excel will look in cell A5 to find the value to
multiply by 6. If you copy the formula in cell B6 to cell G7, the formula
will continue to be =$A$5*6 because you used an absolute cell reference.


Relative Versus Absolute References.  In most instances, you will use
relative cell references. Use an absolute cell reference when you know that
the cell containing the data will not be moved through editing or updates.


Exercise Objectives.  In the following four exercises, you will copy a
formula that uses relative cell references and you will copy a formula that
uses an absolute cell reference.


Exercise: Entering a Formula with Relative Cell References
Complete the following four steps to enter a formula that uses relative cell
references.

Step 1:  Select cell A18 by pressing Ctrl-G to open the Go To dialog box.
Type A18 in the Reference field, and then press Enter. JFW announces, "Gross
A18."

You want to determine the gross income for each quarter. To do this,
subtract the quarterly expense figures from the quarterly revenue figures.
(Cells B7 through E7 contain the quarterly revenue figures; cells B12
through E12 contain the quarterly expense figures.)

Step 2:  Press the Right Arrow key to move to cell B18. Then, type the
following formula: =B7-B12. 

Take a moment to decipher this formula. The equal sign directs Excel to
treat the data as a formula. The cell reference B7 directs Excel to use the
value in that cell. The minus sign indicates Excel will subtract the values
in the referenced cells. The reference B12 directs Excel to use the value in
that cell.

Because the B7 and B12 cell references are relative cell references, Excel
will find the data it needs to perform the calculation by using the current
cell, cell B18, as a starting point. The first reference, B7, causes Excel
to look eleven cells above the current cell (B18) to find the first value.
The second reference, B12, causes Excel to find the second value by looking
six cells above the current cell. Once Excel has determined which values to
use, it will perform the calculation.

Step 3:  Press Enter to accept the formula. JFW announces, "Blank B19."

When you press Enter, Excel finds the correct data, performs the
calculation, displays the gross income for the first quarter in cell B18,
and moves to cell B19.

Step 4:  Press the Up Arrow key to move back to cell B18. JFW announces,
"7800 Has Formula  B18," indicating the gross income value for the first
quarter is 7,800.

You will perform the same calculation for each quarter. Because you used
relative cell references in your formula, you can simply copy and paste the
formula. Excel will automatically update the cell references so that the
correct data will be used.


Exercise: Copying a Formula with a Relative Cell Reference
Complete the following ten steps to copy the formula in cell B18 and paste
it in cells C18, D18, and E18.

Step 1:  Press Insert-C to verify cell B18 is selected. Then, press Ctrl-F2
to read the formula in cell B18. JFW announces, "=B7-B12."

Step 2:  Press Ctrl-C to copy the formula. JFW announces, "Copied Selected
Text to Clipboard." A dotted selection cursor appears around cell B18.

Step 3:  Press the Right Arrow key to move to cell C18. Then, press Ctrl-V
to paste the formula into cell C18. JFW announces, "Pasted Text from
Clipboard."

Step 4:  Press Ctrl-F2 to read the formula in cell C18. JFW announces,
"=C7-C12." 

Notice that Excel automatically updated the cell references to C7 and C12 in
the formula. Excel will continue to find the first value by looking eleven
cells above the current cell (C18). Then, Excel will find the second value
by looking six cells above the current cell.

Step 5:  Press the Right Arrow key to move to cell D18. Then, press Ctrl-V
to paste the formula into cell D18. JFW announces, "Pasted Text from
Clipboard."

Note:  You do not need to copy the formula again because it remains on the
clipboard until you copy new data or close Excel.

Step 6:  Press Ctrl-F2 to read the formula in cell D18. JFW announces,
"=D7-D12." 

Excel automatically updated the cell references to D7 and D12 in the
formula. Excel will continue to find the first value by looking eleven cells
above the current cell (D18). Then, Excel will find the second value by
looking six cells above the current cell.

Step 7:  Press the Right Arrow key to move to cell E18. Then, press Ctrl-V
to paste the formula into cell E18. JFW announces, "Pasted Text from
Clipboard."

Step 8:  Press Ctrl-F2 to read the formula in cell E18. JFW announces,
"=E7-E12." 

Excel automatically updated the cell references to E7 and E12 in the
formula. Excel will continue to find the first value by looking eleven cells
above the current cell (E18). Then, Excel will find the second value by
looking six cells above the current cell.

Step 9:  Press Enter to accept the pasted information in the cells. Excel
performs the calculation. JFW announces, "Enter."

Step 10:  Press the Left and Right Arrow keys to review cells B18 through
E18. Remember, you can use the following keystrokes to obtain more
information:

Press the Numpad 5 key to read the cell contents again.
Press Insert-C to read the cell coordinates again.
Press Ctrl-F2 to read the formula in the cell.
Press Alt-Ctrl-1 to read the first cell of the current row.
Press Alt-4 to read the fourth cell of the current column.


Exercise: Entering a Formula with Absolute and Relative Cell References 
Complete the following five steps to enter a formula that uses absolute and
relative cell references.

Step 1:  Move to cell A19 by pressing Ctrl-G to open the Go To dialog box.
Type A19 in the Reference field, and then press Enter. JFW announces, "Taxes
Due  A19."

You want to determine the taxes due on the gross income for each quarter. To
do this, multiply the tax rate listed in cell B17 by each gross income value
listed in cells B18 through E18. (The tax rate is currently seventeen
percent. You calculated the gross income for each quarter in the previous
exercise.)

Step 2:  Press the Right Arrow key to move to cell B19. 

Tip:  To verify the row label, press Alt-Ctrl-1. JFW announces, "Taxes Due."
To verify the column label, press Alt-4. JFW announces, "Quarter 1."

Step 3:  In cell B19, type the following formula: =B18*$B$17. Then, press
Insert-Up Arrow to verify.

Take a moment to decipher the cell references in this formula.

The B18 cell reference is a relative cell reference. Excel will find the
data it needs to perform the calculation by using the current cell, B19, as
a starting point. Excel will look one cell above the current cell (B19) to
find the first value.

The $B$17 cell reference is an absolute cell reference. Excel will always
look in cell B17 to find the second value. (Remember, the dollar signs
denote an absolute cell reference.)

Once Excel has determined which values to use, it will perform the
calculation.

Step 4:  Press Enter to accept the formula. 

When you press Enter, Excel finds the correct data, performs the
calculation, displays the taxes due for the first quarter in cell B19, and
moves to cell B20.

Step 5:  Press the Up Arrow key to move back to cell B19. JFW announces,
"1326  Has Formula  B19," indicating the tax due for the first quarter is
1,326.

You will perform the same calculation for each quarter. Because you used a
relative cell reference and an absolute cell reference in your formula, you
can copy the formula to each cell. Excel will automatically update the
relative cell reference for the first value and continue to use the data in
cell B17 for the second value.


Exercise: Copying a Formula with Absolute and Relative Cell References 
Complete the following ten steps to copy the formula in cell B19 and paste
it in cells C19, D19, and E19.

Step 1:  Press Insert-C to verify cell B19 is selected. Then, press Ctrl-F2
to read the formula in cell B19 again. JFW announces, "=B18*$B$17."

Step 2:  Press Ctrl-C to copy the formula. JFW announces, "Copied Selected
Text to Clipboard." A dotted selection cursor appears around cell B19.

Step 3:  Press the Right Arrow key to move to cell C19. Then, press Ctrl-V
to paste the formula into cell C19. JFW announces, "Pasted Text from
Clipboard."

Step 4:  Press Ctrl-F2 to read the formula in cell C19. JFW announces,
"=C18*$B$17."

Excel automatically updated the relative cell reference to C18 and left the
$B$17 cell reference intact. Excel will continue to find the first value by
looking one cell above the current cell (C19). Then, Excel will find the
second value by looking in cell B17.

Step 5:  Press the Right Arrow key to move to cell D19. Then, press Ctrl-V
to paste the formula into cell D19. JFW announces, "Pasted Text from
Clipboard."

Step 6:  Press Ctrl-F2 to read the formula in cell D19. JFW announces,
"=D18*$B&17."

Step 7:  Press the Right Arrow key to move to cell E19. Then, press Ctrl-V
to paste the formula into cell E19. JFW announces, "Pasted Text from
Clipboard."

Step 8:  Press Ctrl-F2 to read the formula in cell E19. JFW announces,
"=E18*$B$17."

Step 9:  Press Enter to accept the pasted information in the cells. Excel
performs the calculation. JFW announces, "Enter."

Step 10:  Press the Left and Right Arrow keys to review cells B19 through
E19. Remember, you can use the following keystrokes to obtain more
information:

Press the Numpad 5 key to read the cell contents again.
Press Insert-C to read the cell coordinates again.
Press Ctrl-F2 to read the formula in the cell.
Press Alt-Ctrl-1 to read the first cell of the current row.
Press Alt-4 to read the fourth cell of the current column.

Note: If in the future the tax rate changes, you can enter the new rate in
cell B17. Excel will automatically recalculate the figures in cells B19
through E19.


Tip - Keyboard Shortcut.  To quickly change a relative cell reference to an
absolute cell reference, press F2 to activate the Edit mode. Place the
insertion point after the reference. Then, press F4. 


What You Learned.  In this topic, you learned:

Excel distinguishes between two types of cell references: absolute cell
references and relative cell references.

When you use a relative cell reference in a formula, Excel finds the data
referenced by using the cell with the formula as a starting point. 

When you use an absolute cell reference, Excel finds that data reference
using the exact coordinates of a cell regardless of the position of the cell
that contains the formula.

Excel updates relative cell references if you copy or move a formula to
another cell. Excel does not update an absolute cell reference if you copy
or move a formula to another cell.

Excel uses dollar signs to denote an absolute cell reference.

Use absolute cell references when you are sure the cell reference will not
change through edits or updates.


********************************************************************

NOTICE: The information contained in this email and any attachments 
is confidential and may be privileged.  If you are not the intended 
recipient you should not use, disclose, distribute or copy any of 
the content of it or of any attachment; you are requested to notify 
the sender immediately of your receipt of the email and then to 
delete it and any attachments from your system.

NCBI endeavours to ensure that emails and any attachments generated 
by its staff are free from viruses or other contaminants.  However, 
it cannot accept any responsibility for any such which are 
transmitted.  We therefore recommend you scan all attachments.

Please note that the statements and views expressed in this email 
and any attachments are those of the author and do not necessarily 
represent the views of NCBI


********************************************************************

Other related posts:

  • » [vip_students] Excel Tutorial:Lesson 28,Using Absolute and Relative Cell References