Create or change a cell reference

A cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) refers to a cell or a range of cells on a worksheet and can be used in a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) so that Microsoft Office Excel can find the values or data that you want that formula to calculate.

In one or several formulas, you can use a cell reference to refer to:

  • Data from one cell on the worksheet.
  • Data that is contained in different areas of a worksheet.
  • Data in cells on other worksheets in the same workbook.

For example:

This formula: Refers to: And Returns:
=C2 Cell C2 The value in cell C2.
=Asset-Liability The cells named Asset and Liability The value in the cell named Liability subtracted from the value in the cell named Asset.
{=Week1+Week2} The cell ranges named Week1 and Week2 The sum of the values of the cell ranges named Week1 and Week 2 as an array formula.
=Sheet2!B2 Cell B2 on Sheet2 The value in cell B2 on Sheet2.



    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

    Switch between relative, absolute, and mixed references


    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 (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) 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.


    Range Finder color-codes precedent cells

    Callout 1 The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.
    Callout 2 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   If there is no square corner on a color-coded border, the reference is to a named (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) range.

    1. Do one of the following:

    The reference can be a single cell or a range of cells, and the array formula can be one that calculates single or multiple results.

    Top of Page Top of Page

    Create a cell reference to another worksheet

    You can refer to cells that are on other worksheets by appending the name of the worksheet followed by an exclamation point (!) to the start of the cell reference. In the following example, the worksheet function named AVERAGE calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.


    Sheet reference example

    Reference to a range of cells on another worksheet in the same workbook
    Callout 1 Refers to the worksheet named Marketing
    Callout 2 Refers to the range of cells between B1 and B10, inclusively
    Callout 3 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 (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) 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 nonalphabetical characters, you must enclose the name (or the path) within single quotation marks (').

    Top of Page Top of Page

    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 can use this command to:

    • Easily display important information in a more prominent position. Let's say that you have a workbook that contains many 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.
    • 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 you want to link to.
    2. On the Home tab, in the Clipboard group, click Copy Button image.

    The Clipboard group on the Home tab

    Keyboard shortcut  You can also press CTRL+C.

    1. Click the cell that you want to link from.
    2. On the Home tab, in the Clipboard group, click Paste Button image.

    By default, the Paste Options Button image button appears when you paste copied data.

    1. Click the Paste Options button, and then click Paste Link Button image.

    Top of Page Top of Page

    Change a cell reference to another cell reference

    1. Double-click the cell that contains the formula that you want to change. Excel highlights each cell or range of cells with a different color.
    2. Do one of the following:
    3. Press ENTER, or, for an array formula (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.), press CTRL+SHIFT+ENTER.

    Top of Page Top of Page

    Change a cell reference to a named range

    Frequently, if you have defined a name to a cell reference after you have entered a cell reference in a formula, you may 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.

    Defined Names group on the Formulas tab

    1. In the Apply names box, click one or more names, and then click OK.

    Top of Page Top of Page

    Switch between relative, absolute, and mixed references

    1. Select the cell that contains the formula.
    2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) Formula bar, select the reference that you want to change.
    3. Press F4 to switch between the reference types.

    For more information about the different type of cell references, see Overview of formulas.

    Top of Page Top of Page

     
     
    Applies to:
    Excel 2010