Excel Cell References to a Different Worksheet

Excel uses two formulas to link to different worksheet cells in:

1. the same workbook
2. a different workbook

Cell references to a different worksheet in the same workbook

An Excel workbook is also known as an Excel spreadsheet file.
It is possible to use references to other worksheets in your formulas. In this example you need to place in cell B6 of your Summary worksheet the value from cell B25 in the Recreation worksheet.

Start to type your formula in cell B6 as usual (type an = sign).

Summary

At the point where you want to include the cell reference, select the Recreation worksheet using the tabs at the bottom.

Tab Bar

You will notice that the corresponding prefix "Recreation!" will be automatically inserted into your formula.
Now that you see the active Recreation worksheet select cell B25. DO NOT use the mouse to click back to the original worksheet. Before you can select the original worksheet you must either press the Enter key to confirm the formula or you must insert another operator.

Recreation

When you hit Enter the active page will switch back to the original Summary worksheet.
Note: When your worksheet name contains spaces the reference must be placed within single quotes.

In this example you need to place in cell B3 of your Summary worksheet the value from cell I13 in the June 7 worksheet.

In cell B3 of the Summary worksheet start to type your formula as usual (type an = sign).

Summary

At the point where you want to include the cell reference, select the June 7 worksheet using the tabs at the bottom.

Tab Bar

You will notice that the corresponding prefix 'June 7' will be automatically inserted into your formula. Notice that the worksheet name June 7 has a space and must be in single quotes.
Now that you see the active June 7 worksheet select cell B3. DO NOT use the mouse to click back to the original worksheet or on another cell. Before you can select the original worksheet you must either press the Enter key to confirm the formula or you must insert another operator.

June 7

When you hit Enter the active page will switch back to the original Summary worksheet.

Cell references to a worksheet in a different workbook

It is possible to use references to cells in other workbooks. These workbooks can either be open or closed.
In this example we want to add the value in cell D2 to the value in cell D2 of another worksheet named Sheet1 in another workbook named Book2.
When the referenced workbook is open the formula is displayed as below. The name of the workbook must be surrounded by square brackets.
If the name of the worksheet contains any spaces then the worksheet name must be enclosed in single quotes.

In this example we are looking at Sheet1 in the Book1 workbook and Sheet1 in the Book2 workbook.
Book1 has a value of 5 in cell D2.
Book2 has a value of 10 in cell D2.

Book1 with Book2 Open

Book2 with value of 10 in cell D2

Notice that all cell references that refer to other workbooks are inserted as absolute references by default (see the Relative and Absolute References tutorial).

If the referenced workbook is not currently open then the full directory location of the file can be used. In this case the directory location and the workbook and worksheet name must be enclosed in single quotes.

We now have a value of 15 in cell B2 of workbook Book1. Excel added the values together for a Sum of 15.

Book1 with Book2 Closed

It is possible to type in the cell references directly into your formulas although it is much easier to use the mouse.
To create a cell reference to another workbook, open the other workbook first and then use the Window drop-down menu to switch to the other workbook and select the required cell.
When you create a cell reference to a different workbook the actual data is stored in the other workbook and a copy of the data is just displayed.
The cell references will still update even when the other workbook is closed.
Every time a workbook is opened that contains cell references (or links) to other workbooks you will be prompted as to whether you want to update these links. This is done to get the most recent value from the linked workbook.