Referencing cells outside the worksheet

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.