Understanding References
We use references in Excel to make formulas more dynamic (i.e. less need for maintenance.)
If we were to add two values together, we could write a formula like the following:
This works, but if our data changes, we will be required to revisit the formula and update the values to match the new data.
Instead of typing the actual values in the formula, we can simply refer to the cell addresses that hold the values.
The formula evaluates to the same answer, but when the data changes, we only need update the data. The formula will automatically recalculate when it detects a change in the data.
It’s like the formula says, “Take the value of what’s in cell A1 and add it to the value of what’s in cell A2.” The question still works even when the data changes.
Relative References
When writing a formula, it is common to refer to a cell’s address. If that address is used in adjacent cells, such as the same formula being used down may rows, it is common to make small adjustments to the cell address reference to “look” at a different cell from row to row.
This is known as a relative reference.
Relative Row References
Observe in the above illustration. The formula for the first calculation is subtracting the contents of cell B2 from cell A2. When the formula is copied to the next row, the “2s” is changed to “3s”, producing a revised formula that subtracts cell B3 from cell A3. The next row changes the “3s” to “4s” and the next row changes the “4s” to “5s”.
Relative Column References
In the above illustration, the formula for the first calculation is subtracting the contents of cell A3 from cell A2. When the formula is copied to the next column, the “As” is changed to “Bs”, producing a revised formula that subtracts cell B3 from cell B2. The next column changes the “Bs” to “Cs” and the next column changes the “Cs” to “Ds”.
When you repeat a formula with relative references, the references receive small adjustments. These adjustments allow them to “look” at different cells
Each time the copied formula crosses a row, it increments all its number (row) references by 1. Each time the copied formula crosses a column, it increments its letter (column) reference to the next letter.
Another way to think of it: relative to the direction and distance a formula is copied, the references will be adjusted.
Absolute Referencing
Absolute referencing is where a cell address in a formula remains the same when the formula is copied to other cells.
This is often necessary when a formula references a constant that will be used repeatedly across multiple iterations of a formula.
Observe the illustration above. All the sale transactions are applying the same discount of 10%. Instead of listing the discount for each transaction, the discount is listed once at the top of the sheet. When the discount reference is created for the first formula in cell C4, it needs to be flagged so the resulting copy/paste operation does not change the reference. This is accomplished by adding dollar signs in the reference.
Think of the dollar signs as anchors that keep the reference from moving.
These can be hand-typed, but it is often easier to use the F4 key to convert a relative reference to an absolute reference.
When building a formula, when you select a cell that you don’t want to look away from, press the F4 key to convert the reference from relative to absolute. If you repeatedly press the F4 key, you will cycle through the various relative/absolute combinations.
Mixed References
There are situations when repeating a formula where you have a cell reference and you want to prevent the column reference from changing but need the row reference to change, or vice-versa. This is where you need to establish a mixed reference: where the column is locked but the row is free (or vice-versa.)
Example of locking rows but not columns
Using the below illustration, we have created a formula in cell E5 that references the price in cell B5.
When we copy the formula down to the following rows, we need to look at a different price each time, so we refer to cell B5 and leave the number part of the reference as-is.
Because we intend to repeat the formulas to the next column, we don’t want the “Bs” in the reference to be changed to “Cs”, so we place a dollar sign before the “B” to convert it to an absolute reference. In this way, none of the column references will change when the formula is copied but the row references are free to be altered.
Example of locking columns but not rows
In the next example, we have created a formula in cell E5 that refers to a discount in cell E3.
When we repeat the formula down to adjacent rows, we don’t want the row reference (the number) to change, so we place a dollar sign before the number. We need to look at a different discount for each of the adjacent months, so we leave the letter part of the reference as-is.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
