About cell and range references
From Microsoft Office Web Components
A reference identifies a cell or a range of cells on a spreadsheet and tells the spreadsheet where to look for the values or data that you want to use in a formula. By using references, you can use data that's contained in different parts of a spreadsheet in one formula or use the value from one cell in several formulas.
Spreadsheets use the A1 reference style, which labels columns with letters (A through ZZZ, for a total of 18,278 columns) and labels rows with numbers (1 through 262,144).
To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50.
To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, type a colon (:), and then enter the reference to the cell in the lower-right corner of the range. The following are examples of references.
| To refer to | Use |
|---|---|
| The cell in column A and row 10 | A10 |
| The range of cells in column A and rows 10 through 20 | A10:A20 |
| The range of cells in row 15 and columns B through E | B15:E15 |
| All cells in row 5 | 5:5 |
| All cells in rows 5 through 10 | 5:10 |
| All cells in column H | H:H |
| All cells in columns H through J | H:J |
| The range of cells in columns A through E and rows 10 through 20 | A10:E20 |
Relative and absolute references
Relative references When you create a formula that contains references to cells or ranges, those references are usually based on their positions relative to the cell that contains the formula. In the following example, cell B6 contains the formula =A5. The value is found one cell above and one cell to the left of B6. This is known as a relative reference.
When you copy and paste a formula that uses relative references, the references in the pasted formula automatically adjust to refer to different cells relative to the position of the formula. In the following example, the formula in cell B2, =A1, refers to one cell above and to the left of B2. When the formula is pasted in cell B3, it adjusts to =A2, which refers to the cell that is one cell above and to the left of cell B3.
Absolute references If you don't want a reference to be adjusted when you copy a formula to a different cell, use an absolute reference. To create an absolute reference, type a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1 in the formula =A5*C1, for example, add dollar signs to the formula as follows:
=A5*$C$1
Mixed references You can also make only a row or only a column reference absolute. This is known as a mixed reference. For example, if you want to copy a formula in which a column reference is updated but the row reference is not, put the dollar sign in front of the row number only:
=C$1
To quickly change from relative to absolute references, select the reference in the formula and press F4. Each time you press F4, the reference cycles from absolute column and absolute row to relative column and absolute row to relative column and relative row, and back to absolute column and absolute row.