Lock / Freeze Cell in Formula in Excel & Google Sheets

This tutorial demonstrates how to lock a cell in a formula in Excel and Google Sheets.

 

lock freeze cell formula 4

 

Lock Cells in Formulas

When you copy a formula, cell references in the formula change depending on where you paste. In some cases, you don’t want cell references to change and want to lock them when copying the formula. In the following example, Column D is the result of the multiplication of values from B2 and Column C.

 

lock freeze cell formula 1

 

If you now copy the formula down Column D, both cell references change (B2 to B3 and C2 to C3, etc.).

 

lock freeze cell formula 2

 

In this case, you don’t want B2 to change. To lock it, click on the cell reference in the formula bar (B2), and enter $ before column and row ($B$2). You can also press F4 on the keyboard to freeze a cell.

 

lock freeze cell formula 3

 

As a result, Column D now has cell B2 locked, and values from Column C are multiplied by 5 in every row.

 

lock freeze cell formula 4

 

Notes: In the newer version of Excel, there are new arrays formulas that automatically spill the values to the range, and don’t need any cell locking. These formulas are SORT, UNIQUE, SORTBY, XLOOKUP, etc.

To see, instead, how to lock a cell for editing, see How to Lock Cells.

Lock Cells in Google Sheets

You can use the same logic for locking cells in Google Sheets.