Referencing cells outside the worksheet

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:

S h e e t N a m e ! C e l l A d d r e s s

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:

= [ W o r k b o o k N a m e ] S h e e t N a m e ! C e l l A d d r e s s

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 2009:

=A1*'[Budget For 2010.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 For 2010.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

Note: 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.

Note: 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.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.