How do I change an Excel relative cell to an absolute cell?
By 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 below steps.
- Open Microsoft Excel.
- Highlight the cell containing the formula you wish to have changed into an absolute or relative reference.
- 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.
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.
The above formula adds the values of A1 through A3 and is a basic formula most users should be familiar with.
To change the above relative reference to an absolute reference, add a "$" symbol in front of the column and row.