Absolute references

Introduction to Google Sheets

James Chapman

Curriculum Manager, DataCamp

Relative references

When the referencing cell is moved downwards, the cell references shifts by the same amount.

Relative references change when copied

When the referencing cell is moved left or right, the cell references shifts by the same amount.

Absolute references

The absolute reference didn't change when copied into a different row.

Absolute references don't change when copied

The absolute reference didn't change when copied into a different column.

Will update if the referenced cell is changed

Absolute references: fixing columns and rows

An empty interest column that will be populated using the base payments for each bank and the global interest rate.

Absolute references: fixing columns and rows

The formula for calculating the interest starts with a reference to the base payment.

Absolute references: fixing columns and rows

The formula for calculating the interest starts with a reference to the base payment, then an absolute reference to the global interest rate.

Absolute references: fixing columns and rows

The result of the formula using the global interest rate is shown as 1.2%.

Absolute references: fixing columns and rows

The formula from F2 has been copied into the remaining cells in column F.

Absolute references: fixing columns and rows

The relative reference in F3 shifted to E3, but the absolute reference reference to B7 remains unchanged.

Absolute references: fixing columns and rows

The relative reference in F4 shifted to E4, but the absolute reference reference to B7 remains unchanged.

Absolute references: fixing columns and rows

The relative reference in F5 shifted to E5, but the absolute reference reference to B7 remains unchanged.

Absolute references: fixing rows

Partial absolute references only change when copied in one direction

The partial absolute reference where the row was fixed shifted when copied into a different column.

Absolute references: fixing rows

Partial absolute references only changed when copied in one direction

The partial absolute reference where the row was fixed didn't change when copied into a different row.

Absolute references: fixing rows

An empty tax column that is calculated by multiplying the interest by the global tax rate.

Absolute references: fixing rows

The absolute references in column F fix both the column and row, so they will need changing so only the row is fixed.

Absolute references: fixing rows

The dollar sign in front of the column index in the absolute reference was removed to allow the cell reference to change when copied into different columns.

Absolute references: fixing rows

The updated formula in F2 was copied into the other cells in the column.

Absolute references: fixing rows

The cells in column F were copied into column G.

Absolute references: fixing rows

The cell reference in G2 shows that the column index of the partial absolute reference changed, but the row index remained the same.

Absolute references: fixing rows

The cell reference in G2 shows that the column index of the partial absolute reference changed, but the row index remained the same.

Absolute references: fixing columns

When fixing the column in an absolute cell reference, the reference will change when copied into a different row.

Absolute references: fixing columns

When fixing the column in an absolute cell reference, the reference won't change when copied into a different column.

Absolute references: fixing columns

Use a $ in front of the column index

When the column is fixed in a partial absolute reference, the reference only changes when copied into different rows.

Let's practice!

Introduction to Google Sheets