*From*: "Paul Traynor" <paul.traynor@xxxxxxx>*To*: <vip_students@xxxxxxxxxxxxx>*Date*: Sun, 1 Jul 2007 19:26:45 +0100

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 ********************************************************************

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