Keep Variable Cell Constant – Excel & Google Sheets

This tutorial demonstrates how to keep variable cells constant in Excel and Google Sheets.

 

 

Keep Cells Constant in Formulas

When you copy a formula, cell references in the formula change depending on where you paste. If you want to keep variable cells constant, you can lock them before copying the formula. In this example, Column C is the result of the multiplication of values from A2 (2) and Column B.

 

 

If you copy the formula down Column C, both cell references will change (A2 to A3 and B2 to B3, etc.).

 

 

Say you want to keep cell A2 constant. To do that, click on the cell reference in the formula bar (A2), and enter $ before column and row ($A$2). You can also press F4 on the keyboard to make variable cell constant.

 

 

As a result, Column C will have cell A2 as a constant, and the values from Column B are multiplied by 2 in every row.

 

 

Note: 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.

Keep Variable Cells Constant in Google Sheets

You can use the same logic to make variable cells constant in Google Sheets.