Abstract
Transcript
In simple cases, we either want our references to be absolute or relative, but there are times when we need both. Sometimes you want to copy a formula and have the column for the reference be fixed while the row changes, and vice versa. Excel has an easy way of handling this situation.
Let's take a look.
Here we have a small table of data that represents widget sales over a 3-month period. We have the quantity sold of each widget already in the table. We just need to add Sales, which equals the quantity sold x the price. The price appears in column C.
For January, we just need a simple formula. The formula for Widget A is D9 * C9. And if we copy this formula down, we get the correct result for each widget type.
However, if we try to copy the formulas for January into February and March, we see that we have problems. The reference to quantity—one cell directly to the left—is correct. But the reference to price is completely wrong. That's because our reference to price in column C also changed when we copied the formula.
What we need in this case is a way to make the column for the price reference absolute while allowing the row to remain relative. In other words, we need a way to lock the column and allow the row to change.
Luckily, there's a simple way to do this. Let's undo, and try again.
To lock the column for price, we need to add a dollar sign in front of the letter C so that it won't change when copied.
We now have a partially fixed reference. Let's try to copy the formula again.
As before, copying down works fine. Let's try copying the formula to February and March.
This time, we get the results we're looking for. For the price, Excel has locked the column but allowed the row to change. For quantity, which is fully relative, both the column and the row have been changed.
