Welcome to Excelling with Excel 2007

Working Smarter
this page may load a little slowly as there are several diagrams.
There is also an assignment at the end of the page. Email me for the assignment.

Remember Autofill which we love so much?

• autofill works off relative cell references which means the formula AUTOMATICALLY adjusts the cell references
• all formulas use relative cell references UNLESS you tell Excel to use an absolute cell reference - which we will look at next
• relative references make it easy to copy formulas to other cells.
  EXAMPLE: if C12 contained the formula =C7+C11 and you copied this formula to cell D12, then the formula in D12 would become =D7+D11
• you can see relative cell reference easily when you look at an autofill

Diagram:

Remember, relative cell references happen automatically. Relative cell references let you copy formulas and the formulas will update to reflect their new location.

But there is another cell reference in Excel - it is the absolute cell reference. This is a powerful tool in Excel. Show me about absolute cell references.

Absolute Cell References
there is an assignment at the end of these notes

So far you have used relative cell references. Relative cell references update when formulas are moved or copied and we see relative cell references when we autofill. BUT, there will be situations where you want Excel to keep a cell reference the same.

Example: I want to use the data in cell A1 at all times, I do not want Excel to do what it normally does if I use a formula with A1: update the cell reference to another cell.

An absolute reference always refers to the same cell no matter where the formula may be copied or moved to.

An absolute cell reference is created by using $ signs. If I wanted cell A1 to be an absolute reference - A1 would look like this $A$1. The dollar signs tell Excel the data in this cell will stay the same.

You can create the absolute by either typing dollar signs or, as a power user of Excel, you would tap the F4 key on your keyboard just before the letter of the cell you want to be an absolute. Example: tap F4 before A1.

Diagram 1: In this example we want Excel to multiply the commission rate of 8.5% by the total sales to see what the commission dollars would be for each salesperson. Watch what happens.

DIAGRAM 2: This example is a typical one where you must use an absolute. You want the commission rate to remain 8.50%. Therefore, you must use an absolute.

The best way to see how absolutes work is to work with them. Email me for the assignments on absolutes. Now, you are ready for the next adventure - looking at the error messages Excel can give you.

To the error messages.