Spy Journal 3.0 Filtering the web for you

Excel uses a powerful vector system for managing formulas. Each cell can be linked to many others using formulas. In a simple way of explaining this, 2 cells can be added together in a third cell using a formula that references them. The columns are named alphabetically and the rows numerically.

Thus the first cell is known as A1.

Here are two numbers in cells.

It is easy to create a formula that adds 2 cells, E.g., =A1+A2. Entering this formula in cell A3 effectively sums the first 2 cells into the 3rd cell.

Now lets show these numbers as percentages of the total. This can be done by dividing the first cell into the total like thus: =A1/A3. Lets put this formula into cell B1. This gives a result of 36%. However if we copy this cell B1 to B2 we encounter the concept of relative cell addresses. A1/A3 copied down one cell becomes A2/A4. While we want A1 to become A2, we don't want to change A3 to A4. This is because when cells containing links to cells in the formulas are copied, they convert the cell references relative to where they came from.

An absolute reference on the other hand never changes when it is copied.

If we were to make the A3 reference absolute, when we copy the cell it will remain as A3. This is done by using $ signs in front of the Row and Columns.

More advanced understanding of Absolute References.

In actual fact the absolute references can be performed in 2 dimensions. Thus we can 'lock' references in either a row or a column without effecting the other. This is useful when creating formulas in tables. Here is what Excel Help has to say on this:

For a formula being copied:

Last little trick.

I love keyboard shortcuts. In this case when editing a cell that contains a formula reference, E.g., F22, make sure the cursor is somewhere on the cell reference and then click F4 key. This will cycle you through all the possible options, $F$22, F#22, $F22, F22.