Locking Cell References in MS Excel

In MS Excel, we can paste formulas in different spreadsheet cells as Locking Cell References in MS Excel. The formula automatically updates the references when pasted. This can be seen as follows:

In the above figure, we can see that the column’s reference automatically changes as we paste the SUM function from Column C to Column D. As the formula is pasted in the next column, it sums the values appearing in that column. However, it is also possible that we do not want the reference to update as we paste the formula to other cells automatically.

Take the following figure as an example. We have the sales value in one column and the profit margin in the same column above the sales value. So, we only need to figure out what the profit is. If we follow our earlier approach here, the formula will not work, as the reference will automatically change, giving us errors or incorrect figures:

What we need to do here is lock our cell reference when writing the formula. We can do this by adding a dollar sign before the row and column reference to lock it. In this case, we want our row and column both to freeze while we paste our formula into other cells. Therefore, we will add a ‘$’ sign before both row and column references where the profit margin is stated. This is clearly clarified in the following figure:

With the addition of dollar signs before the row and column references of profit margin. The references will remain constant even if we copy and paste the formula into the cells below. The result is shown in the following figure:

You will notice that the reference to ‘Sales’ is still changing as we paste the formula. This is because we have not added dollar signs before the row or column reference for sales.  What if we are provided with sales of two different products, with two different profit margins, each written on top of that product?

In this case, if we follow the earlier approach and lock both columns and rows. The profit margin will stay the same for the other products as well. Take a gander at the following figure:

The figure shows that the locked reference does not change as we paste the formula to the next column (Column E). Here, we want the profit margin reference to change the column as we paste the formula to the next column. However, when we copy and paste the formula for the subsequent months, we’d like to keep that row intact.

Read More About Logic Testing in MS Excel

To achieve this, we will only add the dollar sign before the row now. As it is the row that we want to freeze. We will remove the dollar sign from the column reference, as shown below:

Now, when we paste this formula on all the cells from D4 to E9, we will get the correct profit figures for both A and B. Take a look at the following figure:

It is clear that Locking Cell References in MS Excel like rows and columns in MS Excel spreadsheets may significantly save the time spent copying and pasting formulae.