How to Create an Anchor Formula in 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.

Video of the Day

Microsoft Excel
Select anchor cell.

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.

Add "$" signs.

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

Drag formula.

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.

Intended results.

Verify that each cell has the intended formula and result.

Show Comments