Create or change a cell reference

A cell reference refers to a cell or a range of cells on a worksheet and tells Microsoft Office Excel where to look for the values or data that you want to use in a formula . With cell references, you can use data that is contained in different parts of a worksheet in one formula, or you can use the value from one cell in several formulas. You can also refer to cells on other worksheets in the same workbook. Here are some common examples:

Create a cell reference on the same worksheet

Create a cell reference to another worksheet

Create a cell reference by using the Link Cells command

Change a cell reference to another cell reference

Change a cell reference to a named range

Create a cell reference on the same worksheet

1. Click the cell in which you want to enter the formula.
2. In the formula bar , type = (equal sign).
3. Do one of the following:
   • Cell reference To create a reference, select a cell or range of cells on the same worksheet.

     Cell references and the borders around the corresponding cells are color-coded to make it easier to work with them.

     ---

     

     The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.

     The second cell reference is C3, the color is green, and the cell range has a green border with square corners.

     ---

     You can drag the border of the cell selection to move the selection, or drag the corner of the border to expand the selection.
   • Defined name To create a reference to a defined name, do one of the following:
     • Type the name.
     • Press F3, select the name in the Paste name box, and then click OK.

   Note There is no square corner on a color-coded border, then the reference is to a named range.

4. Do one of the following:
   • If you are creating a reference in a single cell, press ENTER.
   • If you are creating a reference in a single cell or a range of cells, and are referring to another range of cells, press CTRL+SHIFT+ENTER to create an array formula .

Create a cell reference to another worksheet

You can refer to cells that are on other worksheets by prepending the name of the worksheet followed by an exclamation point (!) to the cell reference. 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.

Refers to the worksheet named Marketing

Refers to the range of cells between B1 and B10, inclusively

Separates the worksheet reference from the cell range reference

1. Click the cell in which you want to enter the formula.
2. In the formula bar , type = (equal sign).
3. Click the tab for the worksheet to be referenced.
4. Select the cell or range of cells to be referenced.

Note If the name of the other worksheet contains nonalphabetic characters, you must enclose the name (or the path) within single quotation marks.

Create a cell reference by using the Link Cells command

Alternatively, you can copy and paste a cell reference, and then use the Link Cells command to create a cell reference. You may want to use this command:

• To easily display important information in a more prominent position. Let's say that you have a workbook with a number of worksheets, and on each worksheet is a cell that displays summary information about the other cells on that worksheet. To make these summary cells more prominent, you can create a cell reference to them on the first worksheet of the workbook, which enables you to see summary information about the whole workbook on the first worksheet.
• To make it easier to create cell references between worksheets and workbooks. The Link Cells command automatically pastes the correct syntax for you.

1. Click the cell that contains the data that you want to link to.
2. On the Home tab, in the Clipboard group, click Copy.
3. Click the cell that you want to link from.
4. On the Home tab, in the Clipboard group, click Paste.

   Paste Options appears following the selection that you have just pasted.

5. Click Paste Options, and then click Link Cells.

Change a cell reference to another cell reference

1. Double-click the cell that contains the formula that you want to change. Microsoft Office Excel highlights each cell or range of cells with a different color.
2. Do one of the following:
   • To move a cell or range reference to a different cell or range, drag the color-coded border of the cell or range to the new cell or range.
   • To include more or fewer cells in a reference, drag a corner of the border.
   • In the formula, select the reference, and then type a new one.
   • Press F3, select the name in the Paste name box, and then click OK.
3. Press ENTER, or, for an array formula, press CTRL+SHIFT+ENTER.

Change a cell reference to a named range

If you have defined a name to a cell reference after you have entered a cell reference in a formula, you often want to update the existing cell references to the defined names.

1. Do one of the following:
   • Select the range of cells that contains formulas in which you want to replace cell references with defined names.
   • Select a single, empty cell to change the references to names in all formulas on the worksheet.
2. On the Formulas tab, in the Defined Names group, click the arrow next to Define Name, and then click Apply Names.
3. In the Apply names box, click one or more names, and then click OK.

See also:

• Create an external reference (link) to a cell range in another workbook
• Use names to clarify formulas