Excel Reference Types
There are four types of references in Excel – relative, absolute, row-absolute, and column absolute. This is all about how the formula references move when you copy and paste. The reference types are indicated by $ symbols before the column letter and/or row number. You can type these manually, or press the F4 key to toggle through the various types.
Relative references
In relative references, the row and column references change for pasted cells relative to the copied cells (i.e. =A1). This is the default reference type, however in most references, you probably want either the row, column, or both to be absolute. Below is a screenshot of a relative reference copied from cell A3 to cell F5:
Absolute references
In absolute references, the same reference is used wherever pasted (i.e. =$A$1). Absolute references work really well when referring to input sources such as date or IDs, often on another sheet. Below is a screenshot of an absolute reference copied from cell A3 to cell F5:
Row-absolute references
In row-absolute references, the row reference is the same compared to the copied cells but the column reference changes (i.e. =A$1). Row-absolute references work well when you have inputs at the top of your sheet, perhaps in a hidden row or as field header. Below is a screenshot of a row-absolute reference copied from cell A3 to cell F5:
Column-absolute references
In column-absolute references, the column reference is the same compared to the copied cells but the row reference changes (i.e. =$A1). Column-absolute references work well when you have inputs on the left side of your sheet, perhaps in a hidden column or as field labels. Below is a screenshot of a column-absolute reference copied from cell A3 to cell F5:
Tips For Using References
To determine the type you should use, consider the active cell as a baseline. If the reference in the cell to copy is above or below, use $[row]. If it is to the left or right use $[column]. If it is above/below and left/right, use both. Get in the habit of always using the appropriate reference type, even when you don’t need to copy cells. You never know when you might need to!
Trackbacks/Pingbacks