The formulas we're dealing with use what we call relative addressing. That means that the formula really deals with spatial relationships instead of absolute relationships.
When we copy a formula, it preserves the spatial relationship (e.g., two cells over) and not the absolute relationship (e.g., cell B4).
Thus, in our example above, the formula in cell D4 really says "Take the value of the cell two over from me, and add it to the value of the cell one over from me". When we copied around the formula, those relationships were preserved and the formulas were changed.