How to use absolute cell references
What if the above data changes as shown below?
The data remains the same. However, marks for each of the subjects are not mentioned now. Instead, we have the total marks in Cell F2 only.
To calculate the marks obtained in English, write the formula below.
= B2 * F2
B2 consists of the percentage marks obtained in English. At the same time, Cell F2 consists of the total marks for all the subjects.
Excel calculates the marks obtained in English as below.
Can we move the same formula (drag and drop it) for the remaining subjects?
The results are bizarre. What has Excel done?
The cell references were relative. As we moved it from one column to another, Excel changed the column reference from F2 to G2. G2 is an empty cell, so, Excel returns zero.
In such a case, we don’t want Excel to change the cell reference (F2) every time the formula is moved. We want to keep it constant.
However, we want the cell reference for percentages to change every time the formula is moved.
Write the formula as follows.
= B2 * $F$2
We have changed the relative reference of Cell F2 into an absolute reference of $F$2.
Unlike relative cell references, an absolute cell reference has a dollar symbol before the column and the row reference. Like $A$1.
However, the cell reference B2 is still the same. This is because we only want to fix the cell reference $F$2 but not B2.
Drag and move it across all the columns
Excel calculates the obtained marks for all the subjects.
This time Excel updated the formula for each next column by changing the cell reference of B2 but not F2.
For example, when moved to Column C, Excel changes the formula as below.
So, the formula changes from:
The “theory” behind this is not as simple as changing the lookup value.
Since you’re changing the formula from a normal one to an array formula, the structure of the formula changes a bit as well. By changing the lookup value to 1, you’re not actually telling the MATCH function to search for the number 1 in the lookup array (last name column).