Briefly discuss how Excel modifies cell references when copying and pasting formulas, including an explanation of absolute, relative, and mixed cell references.

Question

What will be an ideal response?

Answer 1

Excel modifies cell references when copying formulas. While copying formulas, however, sometimes you do not want Excel to change cell references. To keep a cell reference constant when copying a formula or function, Excel uses a technique called absolute cell referencing. To specify an absolute cell reference in a formula, enter a dollar sign ($) before any column letters or row numbers you want to keep constant in formulas you plan to copy. For example, $B$4 is an absolute cell reference, whereas B4 is a relative cell reference. Both reference the same cell. The difference becomes apparent when they are copied to a destination area.

A formula using the absolute cell reference $B$4 instructs Excel to keep the cell reference B4 constant (absolute) in the formula as it copies it to the destination area. A formula using the relative cell referenceB4 instructs Excel to adjust the cell reference as it copies it to the destination area. A cell reference with only one dollar sign before either the column or the row is called amixed cell reference. When planning formulas, be aware of when you need to use absolute, relative, and mixed cell references.