How to Create an Anchor Formula in Excel

Microsoft Excel

Excel provides a formula-copying feature that automatically updates the formula for each cell that it is copied to. When a formula refers to a specific cell, it requires an anchor for the automatic changes to update correctly. Anchors can also be used in conjunction with normal cell references.

Instructions

1. 

   Decide which cells in your formula need to be anchored and which do not. Single cells that are used by every iteration of the formula need to be anchored. Cell references that are different for each iteration of the formula do not need to be anchored.
   Example: A1+B1 should update to A1+B2. A1 will be the cell reference that will be anchored.

2. 

   Insert a "$" sign before the letter and the number of the cell reference to be anchored. Example: Replace "A1" with "$A$1".

3. 

   Select the cell with the formula in it. Drag the black square in the lower-right corner of the cell over the cells for the formula to be copied to.

4. 

   Verify that each cell has the intended formula and result.

Tips & Warnings

• If you move a cell that is referenced by a formula, double-check the formula to ensure there were no unintended side effects and that the reference is still valid.

References

• Microsoft Help and Support: How to Force MS Excel to Always Reference the Same Cell

Resources

• Microsoft Developer Network