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 jot down.
The reference to quantityone cell directly to the leftis 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.
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 retry.
We now have a partially fixed reference.
Let’s attempt 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.
