Why is a mixed reference used in Microsoft Excel?

Answer 1

Most formulas are copied either down or across, but rarely both directions. If a cell needs to be made permanent in the formula it is usually made absolute, locking both the row and column reference like this: $A$2. However, if a formula is being copied down, then the column reference will not change anyway, so it is not absolutely necessary to lock it, but you would need to lock the row like this A$2. If copying across, then the opposite applies, it is the column reference that needs to be locked, but not the row, like this: $A2. For most formulas it is only necessary to lock one or the other. Depending on the way data is laid out sometimes locking just one element is useful. Like you may have something in a column that needs to multiplied by different fixed values, giving a formula that might be something like this:

=$A2*B$2

See the related question below for general information about using different reference types.