Relative, Absolute, and Mixed References

You may already know that in formulas you can use references to cells or ranges of cells. However, be attentive because when copying formulas, the references in them are modified.

Note

Relative reference is a reference that gets modified during the copying of a formula.

Absolute reference is a reference that does not get modified during the copying of a formula.

To make a reference absolute, you need to insert the $ symbol before the column number and before the row number.

For example, the reference B10 is relative, while the reference $B$10 is absolute. Let's work with a table.

In the table, we have a list of university classes and the number of students attending them. Imagine the classes occur at the same time, so they are attended by unique students. We need to calculate the percentage of students attending each class relative to the total number of students in the stream.

For calculating percentages, the following formulas should be in the range D3:D7:

• D3 =C3/C8*100;
• D4 =C4/C8*100;
• D5 =C5/C8*100;
• D6 =C6/C8*100;
• D7 =C7/C8*100.

If we analyze these formulas, we can notice that the references in the numerator change, but the references in the denominator do not.

If we enter the formula =C3/C8*100 in cell D3 and copy it to the cells in the range D4:D7, these references in the formula will be modified. Let's make it so the reference C3 modifies during the copying of the formula, but the reference C8 does not. Thus, the formula in cell D3 should look like this: =C3/$C$8*100.

Note

Mixed reference in which during the copying of the formula either the column number or the row number is modified.

If in the reference, the $ symbol is added only before the column number as in $B10, then only the row number will change during copying, and vice versa, in the reference B$10, only the column number will change, while the row number remains unchanged.