How to change an Excel relative cell to an absolute cell

Spreadsheet formulas are defaulted as a relative cell reference. When a formula in a cell is copied to another cell, it changes to match the new cell. In some situations, you may need to have the formula stay the same and not change, which is known as an absolute cell reference.

Changing a cell from a relative to an absolute reference can be done by following the steps below.

1. Open Microsoft Excel.
2. Highlight the cell containing the formula you want to have changed to an absolute or relative reference.
3. Click the formula box (shown below) or highlight the formula and press the F4 key to switch between an absolute and relative cell reference.

To write manually or create an absolute reference, use the "$" symbol in your formula. Below is a basic example demonstrating the difference between a basic relative and absolute reference.

Relative reference

=SUM(A1:A3)

The above basic formula is one that should be familiar to most users; it adds the values of cells A1 through A3.

Absolute reference

=SUM($A$1:$A$3)

To change the above relative reference to an absolute reference, add a "$" symbol in front of the column and row.

Partial absolute reference

You can also create a partial absolute reference for additional flexibility in the formula and functionality in a spreadsheet.

=SUM($A1:$A3)

In this first example, only the column (A) is an absolute reference, while the row is a relative reference. When you copy this formula to another column and row, it still references column A, but it changes the row based on where it's copied.

=SUM(A$1:A$3)

In this second example, only the rows (1 through 3) are an absolute reference, while the column is a relative reference. When you copy this formula to another column and row, it still references rows 1 through 3, but it changes the column based on where it's copied.