How to Create an Anchor Formula in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
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.

Advertisement

Step 1

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.

Advertisement

Video of the Day

Step 2

Add "$" signs.

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

Advertisement

Step 3

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.

Advertisement

Advertisement

Step 4

Intended results.

Verify that each cell has the intended formula and result.

Video of the Day

Advertisement

Advertisement

references & resources