One of the great attractions of spreadsheets when they started was their ability to quickly replicate complex calculations, once those calculations had been developed for one category. An example might be calculations worked out for one month , which could then be replicated quickly across the whole year.

And most of the time that is how we want it, so we don’t even notice.

However there are times when we want the reference to remain fixed, eg we have today’s date in one cell, and we want lots of cells to refer to that. In that example, we could name the cell DateToday, and use DateToday in any formula, with the added advantage of improved legibility.

Another example is in analysis. Supposing we have a table of prices:

Depending on whether the item in column A is an Apple, Banana or Currant, we want the price in the appropriate column.

We can do this with the formula (for the top row) of =IF($A4=”Apples”,$B4,””). This can be copied down successfully, as normal. The dollar sign indicates that the Column is fixed, but the row can change as normal. This is known as an absolute reference, or anchoring.

If we copy this across, because of the anchoring, it will remain pointing to the first column, and the result will come from the second column. We will have to change Apples to Bananas, but that is all (and we can combine it with an even more powerful use of absolute references to avoid doing that, but that’s for another day!)

In an absolute reference, the column can be fixed (eg $B5), the row can be fixed (eg B$5) or both can be fixed (eg$B$5). The dollar signs can be handtyped,  or you can work your way through the different combinations by pressing the function key .