Absolute and relative cell references
Each cell in a worksheet has a unique reference that describes its position – for example A1. In a spreadsheet, there are two types of cell reference – 'relative cell reference' and 'absolute cell reference'.
Relative cell reference: When copied across multiple cells, the cell formula changes based on the relative position of rows and columns. For example, if you copy the formula =B3*C3 from row 3 to row 4, the formula will become =B4*C4.
Relative reference gives the position of a cell relative to the cell that is referencing it are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.
Absolute cell reference: There may be times when you do not want a cell reference to change when filling cells – for example, referring to a % price increase. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant.
An =$A$1 - The $ tells the spreadsheet not to alter the formula as you drag or copy it to another cell, thus referring absolutely to cell A1 is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both:
- $A$1 – The column and the row do not change when copied
- A$1 – The row does not change when copied
- $A1 – The column does not change when copied