Maintaining the Same Cell During Copy

This tip will allow you to "fix" a cell during a copy to prevent it from being shifted.

Note that this trick also works with cell ranges.


Example

In this example, the commission amount is calculated based on the turnover and the commission percentage using the formula:

=B2*B11

excel table turnover percent fix cell

The result is correct.

Now let's copy this formula downwards:

excel table copy problem fix cell

You can see that there is a problem...

Looking at the formula in cell C9, we can see that both the turnover and the commission rate have shifted (which is quite normal):

excel table copy displacement fix cell

To maintain cell B11 during the copy, there are several possibilities.

First Possibility

This involves adding $ to the reference of the cell that should not be shifted, then the formula becomes:

=B2*$B$11

excel table copy dollar fix cell

Cell B11 will then be maintained during the copy:

excel table copy dollar cell fix

The first $ fixes the column and the second $ fixes the row. In this case, a single $ would have been sufficient (=B2*B$11). When in doubt, just add both $.

Second Possibility

Another solution is to rename the cell that should not be shifted.

To rename a cell, select it and then enter its name (where its reference is displayed), then press Enter:

excel rename cell fix

The new formula is now:

=B2*commission

The result will also be correct after copying:

excel copy renamed cell fix