Hands-On Financial Modeling with Microsoft Excel 2019

Absolute referencing

Sometimes, you will have a formula that contains a reference that you don't want Excel to modify when you copy the formula. For example, let's say we want to calculate commission on sales for each salesperson. This would be Sales × Commission.

As we move down the list, the row number changes so that the reference to the sales that are made by the salespersons moves from H5 to H6, to H7, and eventually to H20, which is the last record in our list.

However, the same commission percentage, which is in cell H2, applies to all salespersons. Thus, when we copy down the list, we need to retain the cell reference, H2, and so we need to lock the cell reference or make it absolute.

We do this by putting the $ sign before the column ...