Cell reference is nothing but referring to the position of a cell which is available in the same sheet or different sheet or even different workbook.
In Excel, each row and column has its own name. Each row is identified by its row number and each column is identified by alphabet. In same way, each cell in Excel has its own name. Such as A1, F26 or W345 – consisting of the column letter and row number that intersect at the cell’s location. When listing a cell reference, the column letter is always listed first.
One advantage to using cell references in spreadsheet formulas is that, normally, if the data located in the referenced cells changes, the formula or chart automatically updates to reflect the change.
If a workbook has been set not to automatically update when changes are made to a worksheet, a manual update can be carried out by pressing the F9 key on the keyboard.
There are three types of references that can be used in Excel and they are easily identified by the presence or absence of dollar signs ($) within the cell reference.
- Relative reference
- Absolute reference
- Mixed references
Relative Reference
Relative cell references are basic cell references that adjust and change when copied or when using AutoFill. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns. By default, all cell references are relative references.
Example:
=SUM(B5:B8), as shown below, changes to =SUM(C5:C8) when copied across to the next cell.
Absolute and Mixed References
There may be times when you do not want a cell reference to change when filling cells. 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 absolute reference is designated in a formula by the addition of a dollar sign ($).
It can precede the column reference, the row reference, or both.
Switch between relative, absolute, and mixed references
- Select the cell that contains the formula.
- select the reference that you want to change.
- Press F4 to switch between the reference types.