How do I change an Excel relative cell to an absolute cell?
Spreadsheet formulas are defaulted as a relative cell references which means that when the formula in a cell is copied from one cell to another, it changes the formula to match the new cell. 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 that column. 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 an 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 basic formula is one that should be familiar to most users; it adds the values of cells A1 through A3.
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.
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 is copied.