See how the B26 reference remains the same even after auto fill. Nice huh? In the example below, the cell reference B26 will always be used for the % sales commission even if you drag the formula using Auto fill.

So you do not want the cell to change when you auto fill your formula to the rest of the sales team. We know that the commission is 5% of their sales- ALWAYS. Lets say you are calculating commissions for sales staff of the Widgets as in the above example. If you copy the formula across rows or down columns, the absolute cell reference remains the same unlike the example above.

If the position of the cell that contains the formula changes, the absolute cell reference remains the same. That’s all pretty straightforward, and once you get used to absolute cell referencing that will be too, let’s jump in.Īn Absolute cell reference is indicated in your spreadsheet by a $ sign around the cell, for example =$A$1 means that when referring to this cell is is ALWAYS in that location. The cell reference of the above formula =SUM(B4:B7) when dragged across to column C adjusts itself to then sum the results of C4 to C7. For Example if the position of the cell that contains the formula changes, the reference is changed. These are basic cell references that adjust and change when copied or using the Auto fill function in Excel. Just what is the difference between an absolute and a relative cell reference in Excel?.Ī relative cell reference is the most widely used cell reference in formulas in Excel.

Define relative cell reference excel