|
|
||
Excel Lesson 15 of 29: Absolute and Relative Reference: the Dollar Sign ($) in ExcelWhen you move a step forward in the knowledge of Excel and when you start developing complex formulas you want to copy/paste them. This is when you need to know about absolute and relative references expressed with the dollar sign ($). Before we go on just remember that what is called a reference is the address of a cell that you refer to with a formula. First a very interesting tip. To add dollar signs to addresses in the formula bar you don't need to click before column letters and row numbers to add the dollar signs. If you want the whole formula absolute select it all in the formula bar and click on the F4 key at the top of your keyboard. If you want only the row number absolute click again on the F4 key. If you want only the column letter absolute click again and if you want everything relative click a forth time. You can do this for the whole formula or for individual addresses in the formula. For example click on D4 in a formula and use the F4 key. It becomes $D$4.... Here is a simple example to show you why you need this knowledge and where it applies.
Here you have a list of name in column A and amounts due in column B. In cell B6 you have the total of amounts due. In column C you want a formula to calculate the percentage of the total amount due by each individual. The formula in C2 is =B2/B6. If you try to copy/paste this formula in C3 in becomes =B3/B7 which is wrong. Indeed you want B2 to become B3 but you don't want B6 to become B7. You need to make the reference B7 ABSOLUTE. You do such a thing with dollar signs. Select cell C2 and click on B6 in the formula bar. Then click on the F4 key at the top of your keyboard and click enter. Now you can copy the formula down and it will become =B2/$B$6, =B3/$B$6 , =B4/$B$6 and =B5/$B$6. To make things even righter go back to cell C2, click on $B$6 in the formula bar and click on F4 again. $B$6 becomes B$6 which is righter. Indeed you only want the row component of the address absolute. The column component doesn't need to be absolute (it can be relative) because you don't intend to copy the formula sideways. Now you can copy the formula down and it will become =B2/B$6, =B3/B$6 , =B4/B$6 and =B5/B$6. This second step is not really necessary but develop the habit of making absolute just what needs to be.
In this second example, it is the row that has to be absolute and the column that may be relative. So in cell C3 the formula can be =B2/$E$2 or more precisely =B2/$E2 So if you develop a formula that you want to copy/paste you must use dollar signs ($). If you want the formula to look at the same address wherever it is copied use 2 dollar signs. If you want to copy it up or down use a dollar sign before the row number. If you want to copy sideways use a dollar signs before the column letter. A formula may contain all kinds of absolute and relative references depending on the need. |
|
|
||
