In Excel, relative and absolute references are used to specify how cell references behave when a formula is copied or moved to another location within the spreadsheet. Let’s break down each type:
A relative reference in a formula changes based on its new location when the formula is copied or moved.
For example, if you have a formula =A1+B1 in cell C1, and you copy this formula to cell C2, the formula will adjust to =A2+B2 because the references are relative to the new location.
An absolute reference in a formula remains constant, regardless of where the formula is copied or moved.
Absolute references are denoted by adding dollar signs ($) before the column letter and row number. For example, $A$1.
For example, if you have a formula =$A$1+B1 in cell C1, and you copy this formula to cell C2, the reference to cell A1 will not change, while the reference to B1 will adjust to B2.
Mixed references have one absolute component and one relative component. You can make either the column or row absolute while keeping the other relative.
For example, $A1 is an absolute column reference but a relative row reference, while A$1 is a relative column reference but an absolute row reference.
In Excel, you can switch between relative and absolute references by manually typing the dollar signs ($) before the column letter and row number, or by pressing the F4 key after selecting the cell reference in the formula bar. The F4 key toggles through different reference types (absolute, relative, mixed) each time it is pressed.