Link Workbooks with Each Other in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Excel has multiple ways to create a link between two workbooks.

  • Referring to a Range with Click
  • Copy and Paste the Range Address
  • Use the Hyperlink Option
  • Manually Write a Formula or a Reference

In this tutorial, we will learn all these methods in detail. So, let’s get started:

Referring to a Range with Click

  1. First, open both workbooks.
  2. Select a cell in the workbook in which you want to create the link.
  3. Now, go to the other workbook and click on a cell.
  4. In the end, go back to the first workbook and hit enter.
link-workbook-with-each-other

Another method is to copy a cell from the destination workbook and paste the link to the workbook where you want to create a link.

link-to-another-workbook
  1. First, copy a cell from the workbook for which you want to create the link.
  2. Afterwards, go to the workbook where you want to create a link.
  3. Now, right-click and then click on “Paste Special”.
  4. Finally, click on the “Paste Link” to create a link to that cell.

And you can also create a direct hyperlink to a workbook. For this, you need to use the hyperlink option.

Note: For this method, you don’t need to open the workbook for which you want to create the link.

Right-click > Link > Insert Link.

hyperlink-to-link-another-workbook

Or you can go to the Insert Tab > Link > Insert Link.

insert-link

This will open a dialog box to insert a hyperlink. And in this dialog box, you need to click on the “Existing File or Web Page” and locate the workbook for which you want to create a hyperlink.

locate-workbook-to-create-hyperlink

Once you locate it, please select the file and click OK to save it. Once you click OK, it creates a link to book2 (another workbook) with the workbook’s name in your selected cell.

link-added-in-a-book

Write a Reference in a Formula

You can also write a reference manually by typing it in the cell. Let’s say you want to refer to book2. There are two ways to do that:

1. When Workbook is Open

When the workbook you want to link to is open, you need to use the name of the workbook, the sheet’s name and then the cell or range address.

use-workbook-name-as-link
  1. First, type an equal sign.
  2. Then, enter the workbook’s name with the file extension and wrap it in square brackets.
  3. After that, enter the sheet’s name, followed by the exclamation mark.
  4. In the end, the address of the cell or the range.

2. When Workbook is not Open

And if you want to refer to a workbook that is not open right now, you can use a reference in the following way.

refer-to-a-workbook
  • First, enter = and an apostrophe.
  • Then, the file’s location to which you want to link to.
  • After that, name the workbook with the file extension and wrap it in square brackets.
  • Next, the sheet’s name, an apostrophe, and an exclamation mark (!).
  • In the end, the address of the range or the cell you want to refer to is in the link.

Get the Excel File

Last Updated: April 28, 2024