Skip to content

Understanding Cell References

What is a Cell Reference

A cell reference is an alphanumeric value used to identify a specific cell in a spreadsheet.

In other words, a cell reference consists of letters and a number, and this combination represents the address of a specific cell. The letter identifies the column, and the number represents the row.

Here are two examples.

Cell B6 is in column B and row 6.

Cell AB12 is in column AB and row 12

Referencing a Range

A range is a group of cells. The reference of a range uses the cell address of the top left cell and the bottom right cell separated by a colon.

 

Here are two examples.

The reference for the range below is B2:D5.

 

The reference for the range below is A1:E3.

Relative, Absolute, and Mixed References

In this article, you have only seen relative references so far.

Relative references are written without any dollar signs ($). On the other hand, absolute references contain two dollar sign characters ($).

For example, the reference A2 is a relative reference while the reference $A$2 is an absolute reference.

You can have mixed references as well. A mixed reference will contain one dollar sign ($) instead of two.

For instance, the reference B$4 is a mixed reference.

Relative References

Relative references rely on the location of the cell that contains the reference and the cell that is referenced. In other words, it matters where the two cells exist relative to each other.

For instance, if cell D5 contains a relative reference to cell B2, it is important to note where cell D5 is located in relation to cell B2.

 

This is because whenever a relative reference is copied to another cell, that reference will change to mimic the same type of relation.

For example, if the previous reference is copied and placed into cell F7, the reference is updated to cell D4.

 

Although the reference has changed, the relative location between the cell that now contains the reference and the cell that is being referenced has remained the same.

Absolute References

Absolute references do not care about the location of the cells at all. These references are absolute regardless of where they are moved or copied.

For example, if cell B2 contains the reference $D$4, then this reference will always reference cell D4 no matter what.

 

If the reference is copied to cell A1, you can see that the reference does not change or adjust in any way.

Mixed References

Looking back at absolute references, the dollar sign character ($) effectively locks the reference values in place.

In other words, if there is a dollar sign before the letter, then the letter will not change. If there is a dollar sign before the number, then the number will not change.

This is important to understand because mixed references only contain one dollar sign. Where that dollar sign is determines how the mixed reference will change when copied to another cell.

The best way to understand this is by looking at an example.

Cell A2 contains a reference to $C3.

 

If this reference is copied to cell B4, only the number changes while the letter stays the same.

Here is one more example.

Cell C4 contains a reference to C$8.

 

When the reference is copied to cell D2, the letter changes while the number remains the same.

 

And that about sums up the basics of cell references. If you have any questions or thoughts, be sure to leave a comment down below!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top