CIS 1055
EXCEL 2007 Help Sheet
This document should be used to reinforce the material taught during lab 7. Specifically, I will go into greater depth on the topic of relative versus absolute addressing and provide additional examples in addition to those you saw in class. I also want to warn you about common error messages you will see in Excel.
Common Error Messages in Excel
While using Excel, you will eventually come across at least 3 error messages that may need explanation.
#VALUE! – This message indicates the cells used in your formula or function contain information that will not return a logical value. For CIS 1055 students, this usually happens when using a label in a formula or function. For example, you could not add the value “5” plus the word “January”. When you see this error, look at all the cells your formula is referencing and make the necessary changes.
#NAME! – This message indicates that Excel is confused about the function name you are trying to use. Most often, the name you typed is misspelled. For example, typing in =SUMM(A1:B1) would bring up the #NAME! error until you corrected the spelling of SUM
#DIV/0 – This message indicates your formula is trying to divide by 0. For example, if you try typing in =A1/B1 and B1 = 0, you will see the #DIV/0 error message. This happens most often by dividing a value by a blank cell. Remember, all blank cells in excel have the value of 0.
Relative versus Absolute
All cell references are relative, absolute, or a mixture of both relative and absolute. An example of a relative address is A1. An example of an absolute address is $A$1. A mixed reference example would be $A1 (note that only the letter A has a dollar symbol, so only the letter is absolute)
Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts.
For example, if A5 contains the formula =A1+A2+A3 and you copy and paste that formula into B5, B5 will automatically change the formula from =A1+A2+A3 to =B1+B2+B3 (see image below)
That is pretty cool because it will save you time from having to type in formulas over and over. Remember, if you drag a relative formula down a spreadsheet it will add numbers to the original formula instead of letters like in the above example.
Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy an absolute references in cell A5 to cell B5, it stays the same in both cells =$A$1+$A$2+$A$3 (see image below)
Test Yourself!
See if you can answer the following questions without using Excel to get the answer. Answers with explanations are given on page 3 of this document.
1. In cell C4 you have the formula =A6+B7. If you copy and paste that formula into cell F4, what will the new formula be?
2. In cell C4 you have the formula =A6+B7. If you copy and paste that formula into cell C8, what will the new formula be?
3. In cell C4 you have the formula =A6+B7. If you copy and paste that formula into cell E6, what will the new formula be?
4. In cell C4 you have the formula =$A$6+$B$7. If you copy and paste that formula into cell R30, what will the new formula be?
(answers and explanations on the next page!)
Answers and Explanations
Question 1 - In cell C4 you have the formula =A6+B7. If you copy and paste that formula into cell F4, what will the new formula be?
Answer – The first thing you do is decide if C4 contains relative, absolute, or mixed addresses. In this problem, everything is relative. You now know that the new formula will be different from the old formula in cell C4.
Next, ask yourself if the formula is moving to the right, down, or diagonally across the spreadsheet. In this problem you are moving to the right which means only the letters of the original formula will change. The numbers will stay intact.
Finally, you must figure out how many cells to the right you are moving. Going from C4 to F4 you are moving 3 cells (C to D, D to E, and E to F). This means you must add 3 letters to each of the relative addresses with which you started the problem.
Solution: =D6+E7
Question 2 - In cell C4 you have the formula =A6+B7. If you copy and paste that formula into cell C8, what will the new formula be?
Answer - The first thing you do is decide if C4 contains relative, absolute, or mixed addresses. In this problem, everything is relative. You now know that the new formula will be different from the old formula in cell C4.
Next, ask yourself if the formula is moving to the right, down, or diagonally across the spreadsheet. In this problem you are moving down the spreadsheet which means only the numbers of the original formula will change. The letters will stay intact.
Finally, you must figure out how many cells down the spreadsheet you are moving. Going from C4 to C8 you are moving 4 cells (4 to 5, 5 to 6, 6 to 7, 7 to 8). This means you must add 4 numbers to each of the relative addresses with which you started the problem.
Solution: =A10+B11
Question 3 - In cell C4 you have the formula =A6+B7. If you copy and paste that formula into cell E6, what will the new formula be?
Answer - The first thing you do is decide if C4 contains relative, absolute, or mixed addresses. In this problem, everything is relative. You now know that the new formula will be different from the old formula in cell C4.
Next, ask yourself if the formula is moving to the right, down, or diagonally across the spreadsheet. In this problem you are moving diagonally across the spreadsheet which means both the letters and the numbers of the original formula will change.
Finally, you must figure out how many cells to the right and how many cells down you are moving. Going from C4 to E6 you are moving 2 cells to the right (C to D and D to E) and 2 cells down the spreadsheet (4 to 5 and 5 to 6). This means you must add 2 letters and 2 numbers to the original formula in cell C4.
Solution: =C8+D9
Question 4 - In cell C4 you have the formula =$A$6+$B$7. If you copy and paste that formula into cell R30, what will the new formula be?
Answer - The first thing you do is decide if C4 contains relative, absolute, or mixed addresses. In this problem, everything is absolute. You now know that the new formula will be the same as in the original formula because when something is absolute, it does not change regardless of how far or how often it is moved.
Solution: =$A$6+$B$7
If you have any questions about this help file please send me e-mail to or just stop in during office hours.
Christopher Biehl
Page 1 of 4