How do you use absolute cell references in a spreadsheet like Excel or Google Sheets?
An absolute cell reference is a reference containing the common column letter and row number labels but around those labels are either
dollar signs. For example, the following references for cell
are all absolute references:
What’s the difference between each of three references?
The purpose of the dollar sign in front of the column label and/or the row label is that this fixes the column or label. This helps when copying a cell and you want the reference of the column or row or cell to remain static .
Don’t get confused with other terms used with spreadsheets that contain the word
. For example, an
have nothing to do with an
. The absolute function is the
formula which takes a number and returns its absolute value (a number without a negative sign).
While these terms have no relation with absolute references, an absolute function could take an absolute reference that would return an absolute value! (mind blown)
Fix Cell Reference
The most common approach when using absolute references is where a formula is fixed to a specific cell. When designing your spreadsheet you may have a reference to a
setting or some other important value that some cells reference but instead of copying the value everywhere you can just place it once and then perform an absolute reference to that cell.
A frequent use of an absolute cell reference is applying a salary increase or inflation increase on expenses. Here is a simple example where a salary increase of
is applied to each staff member:
As you can see from the above snapshot the formula in cell
to calculate the new salary for staff after their salary increase references the shared and common static value in
. As this same formula is used for all rows in column
to make the copying easier the cell
is made absolute by fixing the column and the row
When typing or clicking on the absolute cell to reference in your formula instead of having to type the absolute dollar signs you can use the shortcut key
. You can keep hitting the
key and the absolute reference will cycle through the different types: absolute cell
, absolute row
and absolute column
If you hit the
times it will remove all absolute references from the cell.
Why Not Just Use A Named Range Instead?
Another way of using an absolute-like reference is to give the cell a name. If you have formulas referencing multiple absolute cells it can be difficult trying to track and ensure you’re referencing the right cell.
A way around this is to name your cell ranges with something meaningful. In my example, cell
To name a cell simply click on it and in the cell reference area (where you see the label
) click on it and type the name you want to give that cell:
Then when referencing this range instead of using
you can use the alternative named range
Fix Row Reference
Besides fixing the column and the row labels by making an absolute cell reference you can opt to have the absolute part act on the row label only .
This means when you are copying a cell containing an absolute row reference that the row value will remain the same.
Here’s an example demonstrating an absolute row reference:
In the example above the first part of the formula
the reference in the
function contains an absolute row reference. This checks if a value exists in the header row containing the years.
An absolute row reference helps when you are copying cells down as the row label (the number) stays the same as you are copying the cell down.
As seen in the above spreadsheet snapshot, the formula contained in cell
when this formula is
the formula in
– notice here with the reference in the
function that the column stayed the same and the row stayed the same too.
When the same original cell
the formula in
– notice here with the reference in the
function that the column value changed, but the row value did not.
This is how an absolute row reference works. When you copy an absolute cell down or across the row number in the copied cells will stay the same.
Fix Column Reference
Using the same formula as expressed above you would have noticed in the
parameter of the
function that there was a reference where the column was fixed:
By making the column reference absolute it helps when copying cells across .
Therefore, in cell
, as shown above, the formula keeps the column value static:
. Notice how the reference
keeps the same column label, even though you are copying across (to the right).
When copying the cells
the row label will change, but the column label will not. Therefore, as seen above the cell in
. Notice here the reference has changed to
where the column has stayed the same, but the row value has changed.
This is the nature of absolute column references . The column value remains the same when you are copying across or down.
An absolute reference is a reference used in a cell that
either the column or the row or both. An absolute reference is seen by the symbol
(dollar sign) in front of the column and/or the row.
absolute cell reference
has both the column and row fixed and therefore will have the dollar symbol at the front of both the column label and the row label. For example,
is an absolute cell reference using cell
. Copying an absolute cell reference up, down or across will mean the reference stays the same.
absolute row reference
is where the row is fixed
and therefore will have the dollar symbol in front of just the row label. For example,
fixes the row to
. Copying an absolute row reference up or down will not change the reference, but copying the absolute row reference across will mean the row number stays the same and the column value will change.
absolute column reference
is where the column is fixed
and therefore will have the dollar symbol in front of the column label. For example,
fixes the column to
. Copying an absolute column reference across will not change the reference, but copying the absolute column reference up or down will mean the column stays the same and the row value will change.
Finally, instead of using an absolute cell reference, you could look at using named ranges instead. This might be helpful if you have many different absolute cell references in your spreadsheet.