Excel tutorial: How to freeze the value of a cell?

PixieMe / Shutterstock.com
Freezing a cell consists in indicating that the calculation must be done by always taking the same cell for reference. Find out in this tutorial how to freeze a cell in Microsoft Excel.

For example, when you need to apply the same coefficient to a column, rather than multiplying your cells each time with the same value, you will be able to freeze the value of the reference cell in order to apply it to all the cells of your columns.

Here is a concrete example: I have to apply a coefficient of 30% which represents my margin on the articles of my clothing store.

The calculation formula for cell C4 will be: = B4 * E1 (Purchase price * margin rate i.e. 45 € * 30%)

The formula in cell C5 will be: = B4 + C4 (Purchase price + Margin i.e. 45 € + 13.5 €)

So now we stretch the formulas to the other articles:


The expected calculations will not be correct!

• The reference cell for calculating the cap margin will no longer be E1 but E2 which does not contain any value so C5 displays 0 €
• The reference cell for the calculation of the trouser margin will no longer be E1 but E3 which does not contain any value so C6 displays 0 €
• The reference cell for calculating the sock margin will no longer be E1 but E4 which does not contain any value so C7 displays 0 €

We must therefore freeze cell E1 (30%) to indicate to the spreadsheet that this cell must be used for the entire column. Here are the steps to follow.

1. Place yourself in the calculation bar above the Excel sheet

2. Select the cell to freeze in the formula

In our example, this is cell E1

3. Taper F4

The cell is then highlighted (red) and a $ sign appears around the coordinates of the cell. It is these $ signs that freeze the cell in the calculation.

So now we stretch the formulas to the other articles:


The margin calculations are now correct, because the reference cell is now the value of E1 or 30% for each item.

40 ">This tutorial is brought to you by the trainer Jean-Philippe Parein
Find his course Learn and master Excel from A to Z
in full on Udemy.