Absolute and Relative Cell References

Modules >>
Excel I >>
Absolute and relative cell references

Cell and range references

A reference identifies a cell or a range of cells on a worksheet and tells the program where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas.

Microsoft Excel labels columns with letters (A, B, C, etc.) and labels rows with numbers (1, 2, 3, etc.). To refer to a cell, enter the column letter followed by the row number. For example, C5 refers to the cell at the intersection of column C and row 5. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range. This range reference was used to calculate the average of marks in the previous section. For more information and examples of cell references, please refer to your Help menu. In the Index section, search for "cell references."

Relative cell references

Relative cell references are references to cells relative to the position of the formula. When you create a formula, references to cells or ranges are usually based upon their position relative to the cell that contains the formula. In the following example, cell B2 contains the formula =A1; Microsoft Excel finds the value one cell above and one cell to the left of B2. This is known as a relative referencing.

When a formula that uses relative references is copied into another cell, the references in the pasted formula update and refer to different cells relative to the position of the formula. If the above formula "=A1" were copied and pasted into the cell below it (B3), the formula in cell B3 would be changed to =A2 to refer to the cell that is one cell above and to the left of cell B3 (as shown below).

The above example was formatting to "display formulas" (an option found by going to "Options" under the "Tools" menu and selecting "formulas" under the "View" tab). If this option was set not to view formulas, the values calculated by these formulas would be shown as follows:

Absolute cell references

If you do not want references to change when you copy a formula to a different cell, then use an absolute reference. Absolute references are cell references that always refer to cells in a specific location. Sometimes it is necessary to keep a certain position that is not relative to the new cell location. This is possible by inserting a $ before the Column letter or a $ before the Row number (or both). This is called Absolute Positioning.

For example, if your formula multiplies cell A2 with cell B2 (=A2*B2) and you copy the formula to another cell, both references will change. You can create an absolute reference to cell B2 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell B2, for example, add dollar signs to the formula as follows:

=A1*$B$2

The dollar signs lock the cell location to a fixed position. When it is copied and pasted it remains EXACTLY the same (no relative referencing).

Calculating weighted marks

To calculate the weighted mark for the Susan Anderson, the first student listed in the sample spreadsheet, you would use a formula that divides each of her marks by the maximum value for each assignment, then multiply that value by the weight given to it. For example, if her marks are 8/10, 6/10, and 40/50, and the weight of these assignments are 20%, 20%, and 60% respectively, then the calculation would be 8/10*20 + 6/10*20 + 40/50*60. This calculation would produce her final mark. Click on the cell in which you want to display her final mark. In our sample spreadsheet, this would be cell F5.

To insert a formula reflecting the above calculation using relative referencing, you would enter:

=C5/C13*C14

This calculation would use the values 8/10*20. You might ask why we bothered to reference cells C13 and C14 and just use the formula: C5/10*20. This is because you might want to change the maximum mark or weight of the assignment. By referencing C13 and C14 instead, you can change the maximum mark or weight without needing to make any revisions to the formulas.

For the assignment, you must use absolute referencing. This will allow you to copy the formula used to calculate Susan's mark and paste it into the final mark cells for each of the remaining students. To do this, click on the weighted mark cell for Susan Anderson, and enter the following formula to calculate her final mark:

=(C5/$C$13*$C$14)+(D5/$D$13*$D$14)+(E5/$E$13*$E$14)

When you copy the weighted mark cell for Susan and paste it into the cell below her, the formula will be pasted and revised to show the following:

=(C6/$C$13*$C$14)+(D6/$D$13*$D$14)+(E6/$E$13*$E$14)

As you can see, the absolute cell references for C13, C14, D13, D14, E13, and E14 remained intact. The relative referenced cells C5, D5, and E5, however, changed to C6, D6, and E6 to refer to the assignment marks for the next student, Gautham Nero.

Paste this formula into the weighted mark cells for each of the students in your spreadsheet. You can use a "fill down" feature to apply a formula to additional cells, rather than using the copy and paste functions. To do this, click on the cell that contains the formula you wish to use for the cells below it. You will see a "handle" at the lower right of the cell when it is selected (circled below).

Click on this handle and drag it to cover the cells to which you want the formula copied, then release your mouse button. In the sample spreadsheet, the screen would look like the following:

The formula was applied to the remaining cells and calculated the weighted marks for each of the remaining students. You can test out these weighted marks using a calculator, if you'd like. Once you have a spreadsheet like this set up, the calculation at report card time will take care of itself.

Next, for your assignment, calculate the average of the weighted marks. In the sample spreadsheet, cell F11 contains the average of the weighted marks. The formula for this cell was:

=AVERAGE(F5:F9)

You should now have all the formulas entered for your assignment. If you have any questions, please do not hesitate to email them to the listserv or to the coordinator. Please continue onto the next section on spreadsheet protection.