Files & Computers

Excel has a few ways of referencing cells but the two major ones are "Relative" and "Absolute" cell referencing. These are the two we will be concentrating on in this tutorial.

Relative Cell References are the ones that we come across everyday when using Excel and are simply made up of a column letter and a row number. For example the top left cell available has a relative reference of "A1" because it is in column "A" and in row "1".

This allows us to reference individual cells in our formulas. Let’s see this in action in an example that adds up two columns and puts the results in a third column. 

1. Load up Excel and in cells "A1" down to "A10" enter the numbers 1 to 10.

2. In cells "B1" down to "B10" enter the numbers 11 to 20.

3. In cell "C1" enter the following formula =SUM(A1 + B1)

This formula simply adds the contents of cells "A1" and "B1" to give us the answer 12. The formula does this by referring to cells by their relative cell references. 

4. Rather than retyping a variation of the formula in the 9 cells below "C1", we can simply copy and paste the formula. Select cell "C1" which will give it a thicker border with a black box in the bottom right hand corner.

5. Move your cursor over the black box and your cursor will turn to "+" symbol. Click on the black box and whilst holding down the mouse button drag down to cell "C10" and let go of the mouse button.

This will copy the formula into the remaining cells and because we have used relative cell references Excel was able to guess what we were trying to do and adjusted the formula as it went. If you click on each of the cells in column "C" you will see that the formula in each one has been changed slightly. For example the formula in cell "C4" has been changed from =SUM(A1+B1) to =SUM(A4+B4).

The formula has been altered relatively to work in its new cell and in most cases this is an extremely useful feature of Excel; however there are times when we need to override this. For instance, if we want to multiply the results in column "C" by a set amount which is stored in a cell elsewhere. This is where absolute cell referencing comes in.

To continue on from the previous example, enter a number into cell "F1". This will be where we store the number we want to multiply the results in column "C" by, or our fixed value.

In cell D1 enter the formula =SUM(C1*$F$1)

The $ sign before each part of the cell reference means don’t change this part of the cell reference when its copied pasted into another cell.

So in this example Excel reads "$F$1" as cell "F1" but it knows that the "F" column reference and row "1" reference will remain constant or absolute when copied and pasted into another cell.

Copy and paste the formula in "D1" down to "D10" using the method of highlighting the cell "D1", clicking on the black box and dragging down to cell "D10".

Now look at the formula in cell "D4". It has now changed to =SUM(C4*$F$1) which means the only part that has changed is the cell to be multiplied by our fixed value, this is because this is a relative cell reference. The reference to the fixed value has remained absolute throughout the cutting and pasting process because it is an absolute cell reference.

You can also use a combination of absolute and relative cell references in the same cell reference.

Cell reference "$A1" means the column reference is absolute and the row number is relative. When copied to any cell on row "10", for example, the cell reference will become "$A10" no mater where you put it on row "10".

The same happens in the other axis. For instance "A$1" means the column is relative and the column is absolute. So copying this reference into anywhere in column "D" will change the reference to "D$1".

Carl Nixon has over 20 year's experience of data and business analysis for a wide range of companies including international insurance and utility companies.

Carl now offers his Excel, VBA and Analysis skills on a freelance basis. See his site for more details here

Article Source: ArticlesBase.com - Excel's Relative and Absolute Cell Referencing