Working with Formulas That Reference Other Workbooks and Worksheets

Because a workbook can contain multiple worksheets, you might need to reference a cell in another worksheet or even another workbook file. No problem! As long as you follow the proper syntax, you can type a formula that contains a reference to any file.

If the cell is contained in another worksheet in the current workbook, you need to include the sheet name followed by an exclamation point (!) and then the actual address of the cell. Blank spaces are not allowed. For example, Sheet4!A75, points to cell A75 on Sheet 4 of the current workbook. Anytime you reference another worksheet in the workbook, you must include the exclamation point. If you've renamed a worksheet from its default sheet name, use the sheet name and an exclamation point.

If a referenced sheet name contains spaces, enclose the sheet name in single quotes. For example, 'Jan Sales'!A21. The exclamation point character follows the second quote.

Although you can type a cell reference to another worksheet, it's easier to use the mouse. If you need to reference a cell on another sheet as you're building a formula, click the appropriate sheet tab and then point to the cell you want to use. Excel places the sheet name and cell reference, using perfect syntax, into the formula.

You can reference cells that are not currently visible in the worksheet by scrolling to the cell with the scrollbars. You can even reference a cell in another sheet by clicking the sheet tab and then clicking the cell you want to include.

You can also reference a cell from another workbook. It's best to point to the cell you want to use, rather than worry about making sure that you've used the correct syntax.

Make sure that you open the workbook(s) that contain the cells you want to reference before you begin.

Begin the formula by clicking the Edit Formula button so that the formula appears in the Formula palette. As you construct the formula, switch to the open worksheet by clicking the Window menu, and navigating to the worksheet and cell. After you click the cell you want, use the Window menu and click the file that contains the formula you're working on. When you're done building the formula, click the OK button on the Formula palette.