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