How to Lock Cells and Use Absolute References in Excel

Locking cells in Excel is one of the most useful features in Excel. Absolute references and horizontal and vertical range locking allow you to enter a formula once and use it across your entire worksheet without changing the formula.

CONTENTS

Locking Cells and Absolute References

The key to locking cells and using absolute references in Excel is the “$” symbol. The row or column after the $ symbol is locked. Excel supports “absolute references,” “relative references,” and “mixed references.” Understanding absolute references provides the foundation for applying the other two reference types. Locking rows and columns in an absolute reference means that, even when a formula is moved, it continues to retrieve the required source data from the specified row or column. This feature enables you to use a wide range of Excel functions.

Absolute References

To lock a cell, type a $ sign before both the column letter and row number in the cell address.

An absolute reference keeps a cell address fixed regardless of where the formula is copied or moved.

When using formulas, there are two ways to lock source data cells and ranges within an Excel formula:

Enter it manually: Press SHIFT + 4 to enter a $ sign where needed.
Recommended method: When using an Excel formula, place the cursor on the source data cell reference in the formula bar and press the F4 key. The $ signs cycle through column lock, row lock, and full lock options.

WINDOWS EXCEL: Use the F4 key.
MAC EXCEL: Use “fn + F4”.

When you reference range data from another Excel file in a formula, absolute references are entered automatically.

Excel Mixed References

Among Excel cell-locking features, mixed references let you lock cells horizontally or vertically.

Horizontal cell locking fixes the ROW, while vertical cell locking fixes the COLUMN.

While absolute references are mainly used to define ranges in formulas, horizontal and vertical cell locking is often used for variables.

Locking a Column in an Excel Mixed Reference

This section explains how to lock a COLUMN, or lock a cell vertically, in Excel.

To lock cells, first understand the cell reference format.

Cell addresses can be expressed in two formats, but the format used to explain absolute references is a combination of an uppercase letter and a number.
To lock a cell vertically in Excel, enter a $ sign before the uppercase letter.

To lock a column, type a $ sign before the column letter in the cell address.

Locking a Row in an Excel Mixed Reference

To lock the ROW portion of a cell reference horizontally in Excel, enter a $ sign before the row number.

To lock a row, type a $ sign before the row number in the cell address.

Excel Relative References

With Excel relative references, the cell address is not fixed and changes based on the row and column position.

Excel relative reference: a cell address is not fixed and changes according to its location.

Conclusion

We covered how to use absolute, mixed, and relative references by locking cells in Excel.

Understanding only the definition of an Excel function or how to use it is not enough to apply it effectively.

Reference types for ranges can be adjusted to control how variables move, enabling proper use of Excel functions.