Excel: Relative vs. Absolute Cell Reference

There are two types of cell references: relative and absolute.

Relative and Absolute references behave differently when copied into other cells.

Relative references change (relatively) when a formula is copied to another cell. In other words, they change based on the formula. So, if you copy a formula referencing Cell A1 and copy the formula to Row 2, the "A1" reference will change to "A2" since you copied the formula down a row.

Absolute references, on the other hand, remain constant no matter where they are copied. In other words, they will NOT change when you copy a formula to other rows or columns. This type of cell reference is useful when budgeting, for example. You can see what happens if revenues increase by a specific percentage. When formulas are based on the percentage cell, you can change the percentage using a single cell and all other formulas referencing this cell will update automatically according to the new number in that cell.

Watch this brief tutorial to see exactly what I mean.

Excel Cell Reference Tip

By default, Excel uses the "A1" format when referring to cells.  This means that the "A" is the Column and the "1" is the Row that is being referred to.  Most Excel users understand this; but some spreadsheet programs do not use this referencing.

Instead, other programs use the "R1C1" format when referencing cells.  Excel allows for this format as well.

To specify the format you want to use, follow these steps.

Excel 2007:

Click on the "Office" button and select the "Excel Options" button.

Select the "Formulas" tab on the left menu.

Check the "R1C1 reference style" checkbox in the "Working with Formulas" section and click the "OK" button to save the changes.



Excel 2003:
Click on the "Tools" menu and select the "Options" menu item.  Follow the above instructions from here.

Excel 2010:
Click on the "File" tab and select the "Excel Options" button.  Follow the above instructions from here.

Notice the Column Headers are now numbers instead of letters.



NOTE:  If you prefer the "A1" formatting and need to change Excel back to this preference, simply follow the above steps and uncheck the "R1C1" checkbox.