# About cell and range references

A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links.

By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.

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 |

**Reference to another worksheet** In the following example, the AVERAGE worksheet function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.

Note that the name of the worksheet and an exclamation point (!) precede the range reference.

The difference between relative and absolute references

**Relative references** A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

**Absolute references** An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy a absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.

**Mixed references** A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.

If you want to analyze data in the same cell or range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.

- You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.
- 3-D references cannot be used in array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.).
- 3-D references cannot be used with the intersection operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) (a single space) or in formulas that use implicit intersection (implicit intersection: A reference to a range of cells, instead of a single cell, that is calculated like a single cell. If cell C10 contains the formula =B5:B15*5, Excel multiplies the value in cell B10 by 5 because cells B10 and C10 are in the same row.).

**How 3-D references change when you move, copy, insert, or delete worksheets**

The following examples explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6.

**Insert or copy** If you insert or copy sheets between Sheet2 and Sheet6 (the endpoints in this example), Microsoft Excel includes all values in cells A2 through A5 from the added sheets in the calculations.

**Delete** If you delete sheets between Sheet2 and Sheet6, Excel removes their values from the calculation.

**Move** If you move sheets from between Sheet2 and Sheet6 to a location outside the referenced sheet range, Excel removes their values from the calculation.

**Move an endpoint** If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to accommodate the new range of sheets between them.

**Delete an endpoint** If you delete Sheet2 or Sheet6, Excel adjusts the calculation to accommodate the range of sheets between them.

You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.). In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

Reference |
Meaning |
---|---|

R[-2]C | A relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to the cell two rows up and in the same column |

R[2]C[2] | A relative reference to the cell two rows down and two columns to the right |

R2C2 | An absolute reference (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) to the cell in the second row and in the second column |

R[-1] | A relative reference to the entire row above the active cell |

R | An absolute reference to the current row |

When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command such as clicking the **AutoSum** button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.

### To turn R1C1 reference style on or off

- Click
**Options**on the**Tools**menu, and then click the**General**tab. - Under
**Settings**, select or clear the**R1C1 reference style**check box.