Excel > Excel 2003 Intermediate > Working with Multiple Workbooks

Linking Cells

Cells from different worksheets and different workbooks can be linked. By linking cells, when the cell on a different worksheet is changed, the cell with the link is automatically updated.

This can be useful to create a worksheet that summarises data stored in other worksheets.

Link cells from a different worksheet

  1. Select the cell where you wish to enter the link
  2. Type = to start a new formula
  3. Click the worksheet tab of the worksheet containing the cell you wish to link to
  4. Select the cell that you wish to link to
  5. Press Enter
  6. Excel takes you back to the original worksheet. Select the cell to see the link in the Formula Bar.

    Linked worksheet

    The sheetname is followed by an exclamation mark seperating it from the range of cells.

Cells can also be linked using the Paste Link button in Paste Special.

Link cells from a different workbook

Open all the workbooks needed before creating a link between cells on different workbooks. You won't be able to open them once you start entering the formula.

  1. Select the cell where you wish to enter the link
  2. Type = to start a new formula
  3. Click on the open workbook on the Taskbar
  4. Click on the worksheet tab of the required worksheet
  5. Select the cell that you wish to link to
  6. Press Enter
  7. Excel takes you back to the original workbook. Select the cell to see the link in the Formula Bar.

    The formulae linking the cells will look different dependent upon whether the source workbook is open or closed.

    If the workbook is open, the formula will contain the workbook name enclosed in square brackets followed by the worksheet name and then the cell reference.

    Linked workbook when open

    If the workbook is closed, the formula will contain the path of the closed workbook aswell.

    Linked workbook showing file path

When opening a workbook that contains links to a different workbook, Excel informs you that the workbook contains links to other data sources and asks if you wish to update them.

Update external links

Click Update to update the workbook with any changes made in the source workbook, or click Don't Update to view the workbook as it was last saved.

Follow us on

Facebook  Twitter  You Tube