You use relative and absolute references in your daily life. Suppose you are in your dorm room and you want someone to take a letter to the mailbox. Using a relative reference, you tell the person, "Go out the door; turn to the left and go until you see the staircase. Go down one floor, and at the right of the stairway door is the mailbox." These directions are relative to your dorm room at the time you give the instructions. If you move to a different location, these directions no longer work.
Copying formulas in Excel can be a great advantage. For example, if a formula is complicated, you can create it once and then copy it to other cells. A relative address is cell reference that adjusts the cell references in the formula to correspond to new location of the formula. Excel automatically adjusts the cell references in a copied formula to correspond to the formula's new position in the spreadsheet.
Unless you specify otherwise, Excel uses relative referencing for cell addresses when you enter a formula. For example if you enter this formula in cell C5
=B5 + B5 * C16
All these references are relative. The formula translated into English, would read as follows:
In cell C5, multiply the number in the cell one column to the left in the same row and the number in the cell 11 rows down in the same column. Add the number contained in the cell one column to the left in same row.
Sometimes cell references should not be changed when they are copied. A cell reference that does not change when it is copied is called an absolute reference. Back to our mailbox example. Suppose you forgot to give the letter. So you asked a friend to deliver it later for you. Giving the same directions, which were correct relative to your dorm room would send someone somewhere else entirely from this location. To make sure that the letter gets to the mailbox no matter where you are when you give directions, you must use an absolute reference. You might say, "Could you drop this letter in the mailbox on the first floor of Woodsworth Dorm". No matter where you are when you speak, the mailbox is at one absolute location. Similarly, if you copy a formula and you want to make sure that some terms in a formula don't adjust to a new location, you must designate those terms as absolute reference
You can create an absolute reference by placing a dollar sign in front of the row number and/or column letter in the cell reference. Note that the dollar sign in a cell reference has nothing to do with currency. It is just a signal to Excel that the row or column letter is an absolute reference and should not be changed be changed. A formula can contain more than one absolute reference.
A mixed reference, like B$2, contains an absolute only to the row. Mixed references are useful when either the column or the row reference must remain absolute. There are four types of cell references:
|
Reference |
Example |
|
Absolute column, absolute row |
$A$1 |
|
Absolute column, relative row |
$A1 |
|
Relative column, absolute row |
A$1 |
|
Relative column, relative row |
A1 |
|
A1 |
B1 |
C1 |
D1 |
E1 |
|
A2 |
B2 |
C2 |
D2 |
E2 |
|
A3 |
B3 |
C3 |
D3 |
E3 |
|
A4 |
B4 |
C4 |
D4 |
E4 |
|
A5 |
B5 |
C5 |
D5 |
E5 |
|
Fill A4 to B4 |
B4:___________________________ |
|
Copy A4 to C5 |
C5:___________________________ |
|
Copy A4 to C4 |
C4:___________________________ |
|
Copy A4 to D5 |
D5:___________________________ |
|
Copy A3 to C3 |
C3:___________________________ |
|
Copy A3 to D2 |
D2:___________________________ |
|
Fill A4 to B4 |
B4:___________________________ |
|
Copy A4 to C5 |
C5:___________________________ |
|
Fill A4 to B4 |
B4:___________________________ |
|
Copy A4 to D5 |
D5:___________________________ |
|
Copy A3 to C3 |
C3:___________________________ |
|
Copy A3 to D4 |
D4:___________________________ |
|
Copy A5 to B5 |
B5:___________________________ |
|
Copy A5 to C3 |
C3:___________________________ |
|
Copy A5 to B5 |
B5:___________________________ |
|
Copy A5 to C5 |
C5:___________________________ |
|
Fill B3 to C3 |
C3:___________________________ |
|
Fill B3 to B4 |
B4:___________________________ |
|
Fill B3 to C3 |
C3:___________________________ |
|
Fill B3 to B4 |
B4:___________________________ |
Cell references that do not have data entered into them default to the value zero. The formula will be evaluated with zeros for the missing values and a result will be displayed. When you have a formula that contains a division operator and the denominator results in the value of zero, the spreadsheet will display the text #DIV/0!. For both of these situations, all you have to do is fill in the missing values for the cell references. (The other possibility is that you have typed in the formula incorrectly) Some users like to fill in the formulas at the same time they are entering the values. If you are one of those, these conditions may happen, and all you have to do is fill in the rest of the values.
If cell contains a formula, and that formula contains a reference to itself, an error screen will be displayed stating that you have a circular reference. It will provide you with help. Most likely there is an error in formula or you have incorrectly placed the formula in the wrong cell.