Lesson 17

Preview

Use Absolute Referencing to Calculate

Relative References vs Absolute References

Excel uses two types of cell referencing: relative references and absolute references. By default, Excel uses relative references. What is meant by relative references? Excel uses relative distance (in columns and rows) from a certain cell to determine what additional cells it will use in a formula or function.

Relative References
When we use the AutoFill feature, that is a perfect example of Excel using relative references. We recently summed the data in cells B2, B3, and B4, for the Column B total of 68. That was our starting point. Then we used the Autofill feature to sum the remaining columns C, D, and E, by moving the mouse to the right.

AutoFill for Column C (The following paragraph refers to FIGURE 1 below)

Excel knows that the total of 68 was the result of data in cells B2, B3, and B4. Excel knows we have moved the mouse from Column B over to Column C, as shown below. Excel knows that since we are now one column over to the right, that it must sum data one column over from cells B2, B3, and B4. Which of course would now be C2, C3, and C4. Since it understands it’s using data from C2, C3, and C4, it also knows that we want to sum those 3 cells and put the total in cell C5. How did it know that? Well, it already knows the correct column, Column C, since that is where the mouse is located. How did it know the correct row address of 5? The answer is the same. It is based on the mouse position. Excel knows the correct row address of 5 because we are dragging the mouse across row 5.

Figure 1

AutoFill for Column D (The following paragraph refers to FIGURE 2 below)
Excel knows that the total of 67 was the result of data in cells C2, C3, and C4. Excel knows we have dragged the mouse further over to Column D, as shown below. Excel knows that since we are now one column over to the right, that it must sum data one column over from cells C2, C3, and C4. Which of course would now be D2, D3, and D4. Since it understands it using data from D2, D3, and D4, it also knows that we want to sum those 3 cells and put the total in cell D5. How did it know that? Well, it already knows the correct column, Column D, since that is where the mouse is located. How did it know the correct row address of 5? The answer is the same. It is based on the mouse position. Excel knows the correct row address of 5 because we are dragging the mouse across row 5.

Figure 2

AutoFill for Column E (The following paragraph refers to FIGURE 3 below)
Excel knows that the total of 67 was the result of data in cells D2, D3, and D4. Excel knows we have dragged the mouse further over to Column E, as shown below. Excel knows that since we are now one column over to the right, that it must sum data one column over from cells D2, D3, and D4. Which of course would now be E2, E3, and E4. Since it understands it using data from E2, E3, and E4, it also knows that we want to sum those 3 cells and put the total in cell E5. How did it know that? Well, it already knows the correct column, Column E, since that is where the mouse is located. How did it know the correct row address of 5? The answer is the same. It is based on the mouse position. Excel knows the correct row address of 5 because we are dragging the mouse across row 5.

Figure 3

AutoFill Completed (FIGURE 4)
Cells C5, D5, and E5 populate with data once you drag the mouse over to the right through Column E and release the mouse button.

Figure 4

Absolute References
Absolute references are different from relative references in that absolute referencing is used when we want Excel to discontinue using its default way of referencing, which is using relative references. Using absolute references forces Excel to perform a formula calculation based on a specific cell address or addresses that we want Excel to use rather than another cell address that Excel would automatically use when using its default mode of relative references.

To create an absolute reference requires changing the cell address using a dollar symbol $. Adding a dollar symbol $ forces Excel to use a specific address instead of its default relative address. For example, if we want Excel to use cell F1, we would add dollar symbols to that cell address so it would be $F$1. We are adding a dollar symbol $ to the column address F and we are also adding a dollar symbol $ to the row address 1.

Here’s an example of where we need to use an absolute address with our data.

Four young kids, Tom, Robert, Linda, and Mary are selling apples in their neighborhood. They each have their own apple stand. Their parents helped them begin business by purchasing 200 apples. The 200 apples purchased by their parents were divided into 50 apples per stand. So Tom’s apple stand is starting neighborhood business with 50 apples. The same is true for the other three kids; each of them are starting their own neighborhood business with 50 apples.
After three months of selling apples, here is the result of the four kids efforts, shown in FIGURE 5 below:

Figure 5

Since we need to know the % of Total Apples Sold, which is 200, we must write a formula that will divide each kid’s total by the number of Total Apples Sold, to get a percent of the total for each kid.

So let’s start with Tom. Tom’s total after 3 months of selling was 42 apples sold. To get the percentage of how much of 200 is 42, we would have to divide 42/200. So in cell B6, we would write this formula: =B5/G1 which would give us .21 after we press the enter key. .21 as a percentage would be 21.00%

Looks good so far. But why did we get a Divide/0 error message for the other three children? If we investigate some cell addresses, we can see the answer as to why we have these errors.
We can see the problem by examining the formula bar contents. Let’s look at cell address C6 in FIGURE 6 below. What are the cell addresses in the formula bar? There is data in cell address C5. Is there any data in cell address H1? That is why we have a #DIV/0! error. The same situation repeats for cell addresses D5 and E5. There is data in those cell addresses but the divisor cell address is an address that has no data in it.

Figure 6

Although cell address C5 has good data in it with the number 40, the second cell address of the division shows Excel tried to use cell address H1. Is there any data in cell H1? Excel interprets an empty cell as 0. So Excel attempted to divide 40/0. Any time you divide by 0, the answer will be 0, thus the Divide/0 error message.

If we were to look at the Divide/0 error message for Linda in cell address D6, we would discover =D5/I1 in the formula bar. Since cell I1 is empty, again we have 50/0 which of course as we have already seen will give as another Divide/0 error message. The same thing holds true for Mary, with E5/J1. Since there is no data in cell J1, we will also receive a Divide/0 message.

We have clearly seen that the problem is not with the first cell address of our formula, but rather with the second cell address. We must change the second cell address of the formula to an absolute reference, which will force Excel to use that cell in all of the formula calculations. In this case, that second cell address is G1, which has the number 200 in it. We want that cell address used in computing the child’s % of the total apples sold, for all four children.

The way that we assign an absolute reference to a cell address is done by changing cell address G1 to $G$1. That forces Excel to use cell G1 in all of the four formula calculations. In FIGURE 7 below, if we look at cell address C6, we see that this time cell address G1 was used, so the number 200 in that cell was used in the formula calculation, which is why we did not receive a Divide/0 error message. The same is true for the other two children, Linda and Mary.

Figure 7

Here is a more detailed diagram below illustrating AutoFill usage.

Practice Exercise 9 Use absolute referencing to calculate salesperson % of total quarterly sales

Continue using Excel data file name: SALES DATA FOR SUM AVERAGE MAX MIN

Creating an Absolute Reference
(FIGURE 8) To calculate the salesperson’s % of the total, you would divide the Salesperson Total (B5) by the Quarterly Sales (F5). Since the Quarterly Sales must remain fixed when entering the formula, we must use absolute referencing. You must enter a $ before the column and $ before the row.

STEP 1: (FIGURE 9) The Quarterly Sales total is in cell F5, so it would be $F$5.
Make cell B6 active. Enter =B5/$F$5

STEP 2A: (FIGURE 10) Autofill the remaining columns to get the totals in C9, D9, and E9.

Figure 10

STEP 2B: Highlight cells B9:E9 (FIGURE 11).

STEP 2C: Right-button mouse click the highlighted cells.

STEP 2D: Select Format Cells on the mouse shortcut menu.

STEP 2E: On the Format Cells dialog box, select the Number tab; then select the Percentage choice with 2 decimal places. Select OK button (FIGURE 12).