Introduction
When creating formulas in Excel you may want to repeat the same formula across many rows or columns but keep all these formulas pointing back to one specific cell, column or row.
To achieve this you will need to understand what absolute and mixed references are, and how they differ from relative references.
Download File
If you would like to follow along download the attachment below.
Relative Reference
Relative references are the default setting in Excel and will occur if you enter a cell reference in a formula.
When you enter a cell reference you can tell it is relative, as in the references after the equal sign there will be no $ sign before either the column (the letter) or the row (the number), for example =A1.
When using relative reference, the column and row will move based on the relative position of the rows and columns that you move the formula by, so if you have a formula referencing A1, and you move the formula down one row, the reference will move by one and will now show A2.
In the below example I am trying to work out how much income I have in April based on a formula entered in cell B4 which multiplies the number of units sold (cell B2) by their price (cell B3).
Using relative references if I now copy this formula into the following months the column and row references will move in relation to where they are now located, so when I move the formula from column B over one column, the references are now pointing to column C, when moved over two columns the reference now points to column D.
If I dragged the formula down rather than across then the row numbers referenced would change.
Absolute References
Absolute references keep the cells referenced in your formula fixed, regardless of where you copy or drag a formula to.
An absolute reference is shown when there is a dollar sign ($) in front of the column (letter) and row (number) reference in the formula, for example =$A$1. This example means that no matter where that cell is copied or dragged to it will always reference the value in A1.
Taking the example from above I have made the reference in column B absolute, so the formula is now showing as =$B$2*$B$3.
This means that both values in the formula are absolute references, and when I drag them across columns C to E they are still referencing column B and giving the sum for column B.
How to set an absolute reference
To make a cell reference absolute:
Enter the formula and press F2 or double-click the cell to edit. Note: If you are using a laptop or small keyboard you may need to hold the fn key while pressing F2.
Select the cell reference you want to make absolute.
Press F4 to toggle through the reference options (absolute, relative, and mixed). Note: If you are using a laptop or small keyboard you may need to hold the fn key while pressing F4.
You can also manually add dollar signs ($) before the column and row references.
When to use absolute references
Using the absolute references can be very useful when you have a cell which drives other calculations.
Taking the example used in the relative reference I am now trying to calculate the number of units sold each month as a percentage of the total value of units sold. The easiest way to do this is to have a total column on the right of my data set, column F in the example below, and have each month divide by the total.
To do this for April in cell B3 I have divided the number of units sold that month (cell B2) by the total number of units sold (cell F2).
If I drag my formula across to the right using relative references the calculations for May to July return the '#DIV/0!' error, which shows when you try and divide a value by zero.
The reason why our value for April shows the correct percentage and divides by the total, but the following months show an error and divide by zero is because by using relative references each month that we dragged our formula into, the references for both the month and the total shifted.
So for April we were referencing F2 as the total amount to divide by, but in May the relative reference moved one cell over and was referencing cell G2 as the total, as G2 is blank it is treated as a zero and returned an error.
By using fixed references for the total amount, we can easily calculate the percentage for each month by writing the formula once, and then dragging it to the right.
Now every formula is referencing the total value in cell F2. We can see that the cell is locked by the $ that is showing before the column (the letter) and the row (the number), to give us $F$2.
The units sold value in the formula remains a relative reference, allowing it to change as we drag it to the relevant month.
Mixed References
So far we have seen relative references, where no part of the reference is fixed, and absolute references, where the whole reference is fixed.
There can be times though when you only want to fix just the column or just the row, rather than a specific cell. This is known as a mixed reference.
This is shown with a dollar sign in front of the column, such as =$A1, which means no matter where you drag or copy that reference to the column will always be A, although the row number will change.
With the dollar sign in front of the row, such as =A$1, this would mean that no matter where you moved that to the row would always reference row 1 but the column number would change.
These types of references can be very useful if you are dragging formulas across a range where data is fixed in a row or a column.
In the example below we are trying to calculate the value depending on what the percentage is.
To do this in a way that we only need to set the formula up once and then drag it across the whole calculated range (C3 to E5) we can use mixed references.
The value in column A needs to remain constant, so we put a dollar sign before the A in our cell reference. This is shown in our formula as $A3.
For the percentage we always want to be referencing the percentages in row two, so we put a dollar sign before the 2 in our cell reference. This is shown in our formula as B$2.
This means that when we drag the formula over to the right it will always reference column A in the first part of our formula, and always reference row 2 in the second part of our formula.
We can now drag this across the whole range in our data set to get the answer we want for each individual value, but only had to write the formula once.
Conclusion
Knowing the difference between relative, absolute and mixed references can save a huge amount of time when writing formulas, as when used properly you will only need to write a formula once and then be able to copy or drag it over a range and it will still calculate correctly.
They can also help prevent errors by ensuring that your formula is always pointing to the correct data source.
As your spreadsheets and calculations become more complicated using the correct reference is essential to ensure that you are working as efficiently as possible.
Comentarios