Referencing cells outside the worksheet

Excel 2016 2013 2010 2007 2003
Formulas can refer to cells in other worksheets-and the worksheets don't even have to be in the same workbook. Excel uses a special type of notation to handle these types of references.

Referencing cells in other worksheets

To use a reference to a cell in another worksheet in the same workbook, use this format:

SheetName!CellAddress

In other words, precede the cell address with the worksheet name followed by an exclamation point. Here's an example of a formula that uses a cell on the Sheet3 worksheet:

= A1 * Sheet3!A1

This formula multiplies the value in cell A1 on the current worksheet by the value in cell A1 on Sheet3.

Note: If the worksheet name in the reference includes one or more spaces, you must enclose it in single quotation marks. (Excel will do this automatically if you use the point-and-click method.) For example, here's a formula that refers to a cell on a sheet named All Products:

= A1 * 'All Products'! A1

Referencing cells in other workbooks

To refer to a cell in a different workbook, use this format:

= [WorkbookName]SheetName!CellAddress

In this case, the workbook name (in square brackets), the worksheet name, and an exclamation point precede the cell address. The following is an example of a formula that uses a cell reference in the Sheet2 worksheet in a workbook named Budget:

= [Budget.xls]Sheet2!A1

If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example, here's a formula that refers to a cell on Sheet2 in a workbook named Budget for 2020:

= A1 * '[Budget for 2020.xls]Sheet2'!A1

When a formula refers to cells in a different workbook, the other workbook doesn't need to be open. If the workbook is closed, however, you must add the complete path to the reference. Example:

= A1 * 'C:\Documents\[Budget.xls]Sheet2'!A1

A linked file can also reside on another system that's accessible on your corporate network. The formula below, for example, refers to a cell in a workbook in the files directory of a computer named SOServer.

= '\\SOServer\files\[budget.xls]Sheet2'!$B$7

Notes:

  1. To create formulas that refer to cells not in the current worksheet, point to the cells rather than entering the references manually. Excel takes care of the details regarding the workbook and worksheet references. The workbook that you're using in your formula must be open to use the pointing method.
  2. If you point to a different worksheet or workbook when creating a formula, you'll notice that Excel always inserts absolute cell references. Therefore, if you plan to copy the formula to other cells, make sure that you change the cell references to relative.

See also this tip in French: Référencement des cellules en dehors de la feuille de calcul.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.