Absolute & Relative Cell References

As we saw in the video there’s more than one way to reference a cell within a spreadsheet workbook. have in this lesson we will be practising accessing the data using both absolute and relative cell references.

It is important to remember that one is not necessarily better than the other in all circumstances, so instead we need to consider the way in which we are using the data before deciding upon which one to use.

Relative Cell References

In the previous lessons, we have been using relative cell referencing to access the data in a particular cell in the spreadsheet. When we access data in a specific cell within a spreadsheet we will often see it written with a letter followed by a number.

The letter refers to the column whereas the number refers to the row within the workbook. Sometimes, you will also see the name of the worksheet written with an exclamation mark before it. This means that you are referring to a cell in a different workbook (we will come back to this in a later lesson). For now, all of our relative cell references will simply have a column and row.

In the example below, we have used an equals sign to get the data from another cell. What data do you think would be shown using this simple formula?

spreadsheet_example cell referencing

One of the important things to know about relative cell referencing is that when the cell is copied (along with its formulas) the reference to any other cells will be changed as it is moved. For example, if we copied the formula shown in B3 and pasted it into B4, instead of referring to the data in B2, it would now refer to the data in B3 because the reference is relative to one cell above the formula.

spreadsheet_example relative cell reference

Absolute Cell References

When we want to maintain the reference to a particular cell, row, or column in a spreadsheet formula we can make use of absolute cell references. An absolute cell reference means that when the formula is copied into another cell who reference does not move along with it.

An absolute cell reference is created by adding a $ sign before either the row or column (whichever is to be kept the same). Quite often, we want to keep both the same in order to refer to a specific cell within the spreadsheet say the dollar sign is added before both. Examples of all three types of absolute cell references are shown in the table below.

Reference TypeExplanationExample
Absolute RowThe row will always remain the same, but the column is relative=B$7
Absolute ColumnThe column will always remain the same, but the row is relative =$B7
Absolute CellThe cell reference will never change=$B$7

Activity

Follow the example shown in the video updating your formulas so that they use both relative and absolute cell references. Create a word-processed document and take screen captures of the formulas that you are creating.

Add your screen captures to your Word document and create a guide on how to make use of relative and absolute cell references and identify when it is best to use each type.