Relative and Absolute References in Google Sheets

Relative and absolute references are essential tools in Google Sheets to make your work easier and more efficient. Using both types of references, you can quickly and easily create formulas, links, and other calculations in your spreadsheets. Here’s a guide to help you use relative and absolute references in Google Sheets.

What are Relative and Absolute References?

Relative and absolute references refer to cells or ranges of cells in a spreadsheet. When you create a formula, you can use a relative reference to refer to any cell. If you move the formula to another cell, the references will adjust by the same relative location in the sheet.

If you referred to cell A1 as a relative cell but then dragged the formula down one cell, the reference would also jump down one cell.

On the other hand, an absolute reference will refer to the same cell or range of cells no matter where the formula is moved.

How to Use Relative References

Relative references are the default in Google Sheets, so you don’t have to do anything special to use them. When you create a formula, the references will automatically be relative.

For example, if you create a formula multiplying the contents of cell B14 and cell D4, the formula will be like this:

=B14*D4 

If you then drag this formula across to the right by one cell, the formula will automatically adjust the references by one cell to the right, like this:

=C14*E4 

In this case, it would cause an error since we cannot multiply Jennifer with a blank cell.

If we lock cell B14, however, we can make it absolute to stop it from moving. We can keep the B4 cell relative, so if we drag the formula across to the right, the reference moves along relative to where the formula is, and everything works.

How to Use Absolute References

In Google Sheets, you need to add a dollar sign ($) before the column or row reference in the formula. Using the example before, we wanted to keep the reference of cell B14 static; we don’t want it to move around.

If you place the dollar sign ($) before the column just as we did earlier, then we can move the formula left and right to our heart's content, which will remain absolute to that column.

If we drag the formula up or down with an absolute column reference, it will move up or down with the formula, as we’re only locking it vertically to the column.

Using Relative and Absolute References Together

You can also use relative and absolute references together in the same formula. For example, we can lock the column as before for cell C22 by placing the dollar sign ($) in front of the column.

$C22

Then we can lock the row on the second reference D10 by placing the dollar sign ($) in front of the row.

D$10

With this function, we can drag the formula to the right and down.

=$C22*D$10

By doing this, as we drag the formula to the right, the reference will change, but we’re only allowing this change on row 10.

When we drag the formula down, the reference $C22 is allowed to move vertically but only in column C.

It's also possible to lock a reference to a cell entirely, so it cannot move up and down or left and right. In this case, the dollar sign ($) must be placed both in front of the column and row.

For example, if we wanted to lock a cell such as A1, the formula would need to be written as $A$1. The first dollar sign ($) locks the column ($A), and the second dollar sign ($) locks the row ($1).

Shortcuts

You can use the F4 key on your keyboard to toggle through the options for locking parts of a cell reference. If you click on the part of the formula you want to lock and then hit the F4 key, you will toggle through these options below:

• $A$1 - Lock Column and Row (Absolute)

• A$1 - Lock Row (Absolute Row)

• $A1 - Lock Column (Absolute Column)

• A1 - No locking (Relative)

Final Thoughts

Using relative and absolute references in Google Sheets can save you a lot of time and make your work more efficient.

With just a few simple steps, you can quickly and easily create formulas, links, and other calculations in your spreadsheets.

It takes some time to get your head around the concept, but once you’ve nailed it, you’ll be able to save a ton of time filling in tables of data like this example.