How do I change an Excel relative cell to an absolute cell?
By default, spreadsheet formulas are set up 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.
- Open Microsoft Excel.
- Highlight the cell containing the formula you want 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 portions of the formula and press F4 to have a partial absolute reference.
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.
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.
Partial Absolute reference
You can also create a partial absolute reference, which allows for flexibility in the formula and functionality in a spreadsheet.
In this first example, only the column (A) is an absolute reference, while the row is a relative reference. So when this formula is copied to another column and row, the formula will still reference column A, but it will change the row based on where it has been copied to.
In this second example, only the rows (1 through 3) are an absolute reference, while the column is a relative reference. So when this formula is copied to another column and row, the formula will still reference rows 1 through 3, but it will change the column based on where it has been copied to.