Using External Reference Links

External references in a formula are those that refer to cells outside of the current worksheet, either on other sheets of the same workbook file or in sheets in other workbook files. Because their cells reside outside of the current worksheet, it is not sufficient to specify the cell reference only, as you do with purely local cell references.

When you refer to a cell that resides in another worksheet of the same workbook, you preface the cell address with the sheet name separated by an exclamation point (!), following this syntax:

When you refer to a cell that resides in a sheet in another workbook file, you need to include the filename (enclosed in square brackets) as well as the sheet name (enclosed in a pair of single quotation marks) and cell reference, following this syntax:

Many times, you use external references to create links to values in another sheet that need to be brought forward to the new worksheet. When you set up a link rather than just pasting in a static value, any changes made to the value in the original cell are automatically updated in the linked cell.

The easiest way to create an external reference link is to click the Copy button on the Home tab (Ctrl+C) to copy the cell to the Clipboard. Switch to the cell where you want to set up the link and then paste it with the Paste Link option on the Paste button's dropdown menu (Alt+HVN).