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

Locking Cell References in MS Excel

Unlock the Full Potential of MS Excel: Learn How to Lock Cell References for Accurate and Efficient Data Analysis

In MS Excel, we can paste formulas in different spreadsheet cells as Locking Cell References in MS Excel. 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.

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 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 is clearly clarified in the following figure:

3. Locking Cell References in MS Excel

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 shows 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. Take a gander at 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. 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:

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. Take a look at the following figure:

7. Locking Cell References in MS Excel

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.

 

Ozair Siddiqui
3 min read
Shares

3 comments

    1. Thank you so much for your kind words! We are happy to know that you are enjoying our learning platform and we will continue to strive to provide you with the best learning experience possible.

Leave a comment

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