How to Combine Data from Multiple Sources in Microsoft Excel 2016

Link to data in other worksheets and workbooks

Copying and pasting data from one workbook to another is a quick and easy way to gather related data in one place, but there is a substantial limitation: If the data from the original cell changes, the change is not reflected in the cell to which the data was copied. In other words, copying and pasting a cell’s contents doesn’t create a relationship between the original cell and the target cell.

You can ensure that the data in the target cell reflects any changes in the original cell by creating a link between the two cells. Instead of entering a value into the target cell by typing or pasting, you create a formula that identifies the source from which Excel derives the target cell’s value, and that updates the value when it changes in the source cell.

You can link to a cell in another workbook by starting to create your formula, displaying the worksheet that contains the value you want to use, and then selecting the cell or cell range you want to include in the calculation. When you press Enter and switch back to the workbook with the target cell, the value in the formula bar shows that Excel has filled in the formula with a reference to the cell you clicked.

A 3-D cell reference to another workbook

The reference =‘[FleetOperatingCosts.xlsx]Truck Fuel’!$C$15 gives three pieces of information: the workbook, the worksheet, and the cell you linked to in the worksheet. The first element of the reference, the name of the workbook, is enclosed in brackets; the end of the second element (the worksheet) is marked with an exclamation point; and the third element, the cell reference, has a dollar sign before both the row and the column identifier. The single quotes around the workbook name and worksheet name are there to allow for the space in the Truck Fuel worksheet’s name. This type of reference is known as a 3-D reference, reflecting the three dimensions (workbook, worksheet, and cell range) that you need to point to a group of cells in another workbook.

TIP

For references to cells in the same workbook, the workbook information is omitted. Likewise, references to cells in the same worksheet don’t use a worksheet identifier.

You can also link to cells in an Excel table. Such links include the workbook name, worksheet name, the name of the Excel table, and row and column references of the cell to which you’ve linked. Creating a link to the Cost column’s cell in a table’s Totals row, for example, results in a reference such as =‘FleetOperatingCosts.xlsx’!Truck Maintenance[[#Totals],[Cost]].

Click to view larger image

Link to an Excel table value in another workbook

Important

Hiding or displaying a table’s Totals row affects any links to a cell in that row. Hiding the Totals row causes references to that row to display a #REF! error message.

Whenever you open a workbook containing a link to another document, Excel tries to update the information in linked cells. If the app can’t find the source, as would happen if a workbook or worksheet is deleted or renamed, an alert box appears to indicate that there is a broken link. From within that alert box, you can access tools to fix the link reference.

A dialog box that indicates that the workbook just opened contains one or more broken links

If you enter a link into a cell and you make an error, a #REF! error message appears in the cell that contains the link.

Click to view larger image

Cells that contain incorrect links display a #REF! error

To fix the link, click the cell, delete its contents, and then either retype the link or create it with the point-and-click method described in the procedures for this topic. Excel might also display errors if the cell values in the worksheet cells you link to change in value and cause errors such as DIV/0! (divide by zero).

TIP

Excel tracks workbook changes, such as when you change a workbook’s name, very well. Unless you delete a worksheet or workbook, or move a workbook to a new folder, odds are good that Excel can update your link references automatically to reflect the change.

To create a link to a cell or cell range on another worksheet

1. Start creating a formula that will include a value from a cell or cell range on another worksheet.
2. Click the sheet tab of the worksheet with the cell or cell range you want to include in the formula.
3. Select the cell or cells to include in the formula.
4. Press Enter.

To create a link to a cell or cell range in another workbook

1. Open the workbook where you want to create the formula that references an external cell or cell range.
2. Open the workbook that contains the cell or cell range you want to include in your formula.
3. Switch back to the original workbook and start creating a formula that will include a value from a cell or cell range in the other workbook.
4. Display the workbook that contains the cell or cell range you want to include in the formula.
5. Click the sheet tab of the worksheet with the cell or cell range you want to include in the formula.
6. Select the cell or cells to include in the formula.
7. Press Enter.

To create a link to cells in an Excel table

1. Start creating a formula that will include a value from cells in an Excel table.
2. Click the sheet tab of the worksheet with the Excel table that contains the cells you want to include in the formula.
3. Select the cell or cells to include in the formula.
4. Press Enter.

To open the source of a linked value

1. Open a workbook that contains a link to an external cell or cell range.

2. On the Data tab of the ribbon, in the Connections group, click the Edit Links button.

   

   Manage workbook links by using the Edit Links dialog box

3. In the Edit Links dialog box, click the link you want to work with.
4. Click the Open Source button.

To fix a link that returns an error because it references the wrong workbook

1. Click the Edit Links button.
2. In the Edit Links dialog box, click the link that returns an error.
3. Click Change Source.
4. Click the workbook that contains the correct source value.
5. If the Select Sheet dialog box appears, click the worksheet that contains the correct source value, and click OK.
6. Click Close.

To break a link

1. In a workbook that contains a link to a cell on another worksheet or in another workbook, click the Edit Links button.
2. In the Edit Links dialog box, click the link you want to edit.
3. Click the Break Link button. When prompted, click Break Links to confirm that you want to break the link.
4. Click Close.

TIP

If you can’t easily fix a link that returns an error, the best choice is often to delete the link from the formula and re-create it.