How to Link Excel Workbooks – 4 Methods

You have sales data of different salesmen in the “Sales Data” sheet of the “Reference Workbook” workbook.

dataset 1

To extract “Yearly Sales” from the Reference Workbook to a sheet named “Sales Summary” in the “Link Excel Workbooks” workbook:

dataset 2


Method 1 – Link Excel Workbooks Using the Paste Link Option

  • Select the cells in the “Yearly Sales” column (“Reference Workbook”) and right-click.
  • In the context menu, click Copy.


  • Open “Link Excel Workbooks”.
  • Right-click C5 (first cell of “Yearly Sales” column)
  • In the context menu, click Paste Link in Paste Options.

link Excel workbooks

The “Reference Workbook” will be linked to the current workbook.

paste link

If you click any of the linked cells, you can see it refers to another workbook in the formula bar.

link Excel workbooks

Method 2 – Switching Between Two Workbooks

You can link workbooks by creating a formula and switch between these workbooks.

  • Open both workbooks.

both workbooks

  • Enter = in  C5 (first cell of the “Yearly Sales” column).



  • Go to the “Reference Workbook” and click G5 (first cell of “Yearly Sales” column in the “Reference Workbook”)

link Excel workbooks


  • Go back to the first workbook.

The selected cell of the first workbook (“Link Excel Workbooks”) refers to a cell of the second workbook (“Reference Workbook”).

link Excel workbooks


  • Press ENTER.

C5 will be linked to G5 of the other workbook.

link Excel workbooks

Excel automatically applies Absolute reference. You need to convert this absolute reference into a relative reference.

  • Delete the dollar sign ($) in the formula.

link Excel workbooks


  • Press ENTER.
  • Drag down the Fill Handle to see the result in the rest of the cells.

Method 3 –  Entering a Formula Manually

  • Enter the following formula in C5,
='[Reference Workbook.xlsx]Sales Data'!G5

Reference Workbook.xlsx refers to the second workbook and Sales Data refers to the datasheet containing data that will be linked to that workbook. G5 is the first cell of the “Yearly Sales” column in the Sales Data sheet of the second workbook.

link Excel workbooks

  • Press ENTER.

C5 will be linked to G5 of the other workbook.

link Excel workbooks


  • Drag down the Fill Handle to see the result in the rest of the cells.

The “Reference Workbook” will be linked to the “Link Excel Workbooks” .

link Excel workbooks

If you close the second workbook, the formula in the first workbook will automatically change:

='C:\Users\User\Desktop\Reference Workbook.xlsx]Sales Data'!G5

Here, C:\Users\User\Desktop is the location of the linked workbook.

link Excel workbooks

Method 4 – Linking Excel Workbooks Using a Named Range

Use a Named Range. First,

  • Open the “Reference Workbook” and select the cells in the Yearly Sales column
  • Go to Formulas > Defined Names > Define Name.

used range

  • In New Name, enter a name for the selected cells in Name.
  • Click OK.

name box


  • Open “Link Excel Workbooks” and enter the following formula in C5:
='Reference Workbook.xlsx'!Yearly_Sales

The formula will link the named range to this workbook.

link Excel workbooks

If you use Excel 365:

  • Press ENTER.

If you use any other version,  press CTRL+SHIFT+ENTER for the array formula.

Workbooks will be linked.


Things to Remember

When you close the workbook, Excel disables all links used. You have to enable them by clicking Enable Content in the SECURITY WARNING dialog box, after reopening the workbook.


Check if the Automatic option is checked in Formulas > Calculation Options > Automatic. Otherwise, the linked cells of your workbook won’t change automatically.

automatic calculation

Download Practice Workbooks

Linking Workbooks in Excel: Knowledge Hub

<< Go Back To Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF