Reference number: CH000774

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

Question:

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

Answer:

By default, formulas and spreadsheets are setup using 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 below steps.

1. Open Microsoft Excel.
2. Highlight the cell containing the formula you wish to have changed into an absolute or relative reference.
3. In the formula box as shown below, highlight the complete 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. 

If you wish 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, simply add a "$" symbol in front of the column and row. 

Additional information:

• See our absolute cell, relative cell, and formula computer dictionary definitions for additional information about these terms and related terms.