How Do You Link Workbooks in Excel?

Excel Tips: Linking Workbooks

Creating links between your workbooks lets you quickly include cells or cell ranges in calculations performed on a summary tab. For example, you can store product prices on a Master Pricing tab and link to it elsewhere. A marketing manager may have a data sheet for each geography that he wants to combine in a dashboard. Learn how to complete these and similar linking tasks in Excel.

Both a link and external cell reference dynamically include data from another worksheet. The source worksheet is the worksheet with the data. The destination worksheet has the link formula or external cell reference. If a referenced cell value changes, the destination cell updates when activated.

How Can You Create the Worksheet Link?

IMPORTANT NOTE: Open the destination worksheet and each source worksheet in a single Excel session. Otherwise, you won’t be able to link across workbooks.

There are two ways to create a worksheet link.

Method #1

1. Go to the destination worksheet, click in the applicable cell, and type an equal sign = (Do NOT press ENTER).
2. Go directly to your source worksheet, select the appropriate cell. The system surrounds the source cell with squiggly lines. Press Enter.
3. You are returned to the destination cell, which now displays the data from the source cell.

Method #2

1. Select the source cell and then select Home > Copy.
2. In the destination cell, follow the instructions for your version of Excel:
   • Excel 2007 and up: Go to the Home tab. Under Paste choose Paste Link.
   • Excel 2003 and prior: Navigate to the Edit menu, select Paste Special, and select Paste Link.
3. From the source worksheet, use the ESC button to get rid of the animated border.

How Can You Link a Range of Cells?

You can also link a range of cells by following these steps:

1. Select the cell range you wish to duplicate.
2. Click Copy.
3. On the destination sheet, click the cell where you want the range of data to begin, select Paste Link.

Each cell will have a unique link formula that references the source worksheet.

Can You Manually Enter Link Formulas in the Same Workbook?

When the worksheets share the same workbook, you can manually enter the formula.

1. Type an equal sign, the name of the source sheet, “!”, and the cell. Example: =Atlanta!B6.

IMPORTANT: If your worksheet name has spaces or special characters, you have to include single quotes. Example: =’This Worksheet’!B6.

How Do You Link to Worksheets in Another Workbook?

For worksheets in different Excel workbooks within the same folder, your formula should include the workbook name in brackets. Here is the syntax:

=[Filename.xlsx]Sheetname!CellReference

NOTE: For Excel versions older than 2007, add the xls extension.

For workbooks in different folders, you need to include the full file path. In this case, manual entry isn’t recommended.

What Is the Automatic Calculation Function?

The Automatic Calculation function makes sure that the external cell reference updates whenever the source information does. Here’s how to check this function:

• In Excel 2007 to Excel 2019: Go to the Calculation section on your Excel ribbon. Click the arrow beside Calculation Options, then select Automatic.
• Click File>Excel Options. Click Formulas and click on the “Automatically” option listed beneath Calculation Options.

Who Is Thee Best San Jose Managed IT Support Company?

BC Networks is the best San Jose managed IT support company and can offer support and implementation services for all your Microsoft Office needs. Contact us today for more information.

Dave Brewer

Experience and strategy are what set us apart from other San Jose, Silicon Valley & South Bay IT companies. We deliver consistently optimal results following our carefully developed and mature set of IT practices and procedures.