Relative and Absolute References

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:

Absolute Reference example

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 .

About spreadsheetexpertblog

Working as a computer professional since 1983, on projects varying in size from one person to multi-million pounds. Working on Excel projects since the mid 1990s.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s