How do I change an Excel relative cell to an absolute cell?

Microsoft ExcelBy default, spreadsheets formulas are setup as relative cell references, which means when the formula in a cell is copied from one cell to another, it changes the formula to match the cells it has moved to. For example, if you have a formula that adds cells down a column and copy that formula to another column, the values automatically change to the values of the column you copied it to. 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 into an absolute or relative reference.
  3. In the formula box as shown below, click the formula box or highlight the formula and press the F4 key to switch between an absolute and relative cell reference.

Tip: You can also highlight only portions of the formula and press F4 to have a partial absolute reference.

Microsoft Excel

If you want to write manually or create your own 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 formula adds the values of A1 through A3 and is a basic formula most users should be familiar with.

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.

Additional information