| Microsoft Excel is built on a regime of Columns and | | | | this time round that the cell addresses didn't change. |
| Rows with the intersection of these two elements | | | | The reason is that by adding the $ symbols to the |
| giving us our cells. The cells in Microsoft Excel are | | | | cell address we are telling the formula that regardless |
| always named Columns and then Rows, so a typical | | | | of where you place the formula you must absolutely |
| cell address would look something like - AB256. This | | | | refer to Column B and Row four thus we have $B$4. |
| particular cell is found on column AB and in row 256. | | | | There are many reasons why you may use Relative |
| The reference I wrote there is very important as it | | | | References over Absolute references and vice versa. |
| tells me something significant, that is, it tells me the | | | | One of the most common one for using absolute |
| cell address is a relative cell address. So what does | | | | references is when you have a specific value you |
| this mean? Well there are two types of references | | | | want to refer to in a formula. For example, lets say |
| used in Microsoft Excel which is an Absolute | | | | you are building a mortgage calculator and you need |
| Reference and a Relative Reference. On a | | | | to refer to an Interest Rate. To ensure you are |
| day-to-day basis, the relative and absolute reference | | | | always referring to the right cell that has the interest |
| doesn't really affect the operations of your | | | | rate value you may set an absolute value. In |
| spreadsheet. Where it does become a noticeable | | | | Microsoft Excel there is also a reference called a |
| issue is when you start copying formulas from one | | | | mixed reference. Essentially what this means is that |
| cell to another. Lets investigate the difference | | | | only either the Column or the Row has the dollar |
| between the two … A Relative Reference is one | | | | symbol, for example $C3. What this is telling us is |
| that when copied from one position to another will | | | | that in the formula you must absolutely refer to |
| adjust the formula cell address to suit the position it | | | | column C but the value in the row is relative to the |
| is in. For example if you have a formula in cell address | | | | position of the formula. There are a number of ways |
| C4 that was =B4*C1 and then copied the formula | | | | that you can enter Relative and Absolute values into |
| into say C5 what you will notice is that the cell | | | | a formula. One technique is that you can simply type |
| addresses of the formula will change to =B5 * C2. | | | | the $ symbols next to the Row or the Column. |
| The reason this has occurred is that the cell | | | | However there is an alternative. Once you have |
| addresses are in fact relative addresses. That is the | | | | typed in the Cell address like C4 you can move back |
| cell address is relative to its current position. To | | | | into the cell address and then use the F4 key to |
| change a cell address from being relative to being | | | | toggle the cell reference from Relative to Absolute |
| absolute we simply add dollars signs to the cell | | | | to a Mixed References. Absolute and relative |
| address in this way - $C$5. What this address is now | | | | references are extremely important in Microsoft |
| saying to us is that we must absolutely refer to | | | | Excel and they ensure that the formulas you are |
| column C and absolutely refer to Row 5. This means | | | | creating actually refer to the correct cells. Remember |
| that if we apply the dollar symbols to our previous | | | | one simple rule, if the cell addresses have a $ symbol |
| formula in cell C4 which is =$B$4 * $C$4 and then | | | | next to it, it means you must absolutely refer to |
| copy our formula to cell address C5 you will notice | | | | either the column or the row. |