Blog Home / Data Analysis / Locking Cell References in MS Excel

Locking Cell References in MS Excel

In MS Excel, we can paste formulas in different spreadsheet cells, and 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… Continue reading Locking Cell References in MS Excel

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

1. Locking Cell References in MS Excel

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.

For example, consider the following figure, where we need to compute the profit value from the sales value provided to us in one column and the profit margin written in the same column above the sales value.

If we follow our earlier approach here, the formula will not work, as the reference will be automatically changing, giving us errors or incorrect figures:

2. Locking Cell References in MS Excel

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 can be seen in the following figure:

3. Locking Cell References in MS Excel

Now, when we drag or paste this formula in the below cells, the reference of profit margin will not change, as dollar signs are added before the row and column reference of profit margin. The result can be seen in the following figure:

4. Locking Cell References in MS Excel

You will notice that 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 product as well. This can be seen in the following figure:

5. Locking Cell References in MS Excel

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, but we want the row to be unchanged as we paste the formula down for other months. 

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:

6. Locking Cell References in MS Excel

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. This can be seen in the following figure:

7. Locking Cell References in MS Excel

As can be seen, locking rows and columns can help save a lot of time when pasting formulas in MS Excel spreadsheets. 

 

Ozair Siddiqui
3 min read
Related:
Data AnalysisCPD
Locking Cell References in MS Excel
Ozair Siddiqui 20 January 2023
Data AnalysisCPD
Logic Testing in MS Excel
Ozair Siddiqui 20 January 2023

Shares

Leave a comment

Your email address will not be published. Required fields are marked *