Excelbud

Owner and site operator for Excelbuddy.com

The following tutorial will go over the difference between relative and absolute cell referencing in Excel.

Have you ever copied a formula to a different cell and it no longer worked? In most cases, this is primarily due to the cell referencing in the formula.

Relative Cell Referencing

This is the most common type of referencing.  Relative cell referencing is used to perform simple tasks such as Auto Fill.   This type of referencing changes/adjusts when copied in the spreadsheet.

Example: =SUM(B2:B5)

Absolute Cell Referencing

This type of referencing essentially locks part of the formula to a specific row and column.  This is achieved by using “$”

That’s right, a dollar sign.  $$$

Example: =SUM($B2:$B5)

In this example we are telling the formula to take Cell A3 and multiply $245.54 by 2.3% in Cell E2.  By using dollar signs around Cell E2, we can hold this cell constant.  Using the Auto Fill feature, copy the same formula down the spreadsheet.

Summary of Cell Referencing

$A1 – Allows row reference to change, not the column

A$1 – Allows column reference to change, not the row

$A$1 – Allows neither the column nor the row reference to change.