Linking, Consolidating, and Combining Data

Linking Workbooks

As you know, a cell reference in Excel (like “A7”) simply refers to the cell at the intersection of column A and row 7. A cell reference like “Store!A7” refers to the cell at column A and row 7 in the worksheet named Store. Taking this one step further, the reference “[Sales.xlsx]Store!A7” refers to cell A7 in worksheet Store in the Sales workbook.

This kind of linking can be useful if you have several sheets that reference the same pool of data. If you create links from the different sheets to the source data, the linked cells in the other worksheets or workbooks will be updated automatically when you update the source data. (It is important to note that both workbooks must be open at the same time for updating to occur.)

To link two workbooks, open both of them.

• First, select source data from the source workbook. Next, click Home → Copy. 
• Now, switch workbooks and right-click on the cell in the destination workbook that will contain the linked data. (If you are linking a range of data, right-click on the top left cell of the destination range.)
• Click Paste Link:
  
• Alternatively, click Paste Special and then click Paste Link in the dialog:
  
• The linked data will now appear in the destination workbook. If you update the source data, the linked data will be updated as well.
• Here are two different workbooks: 
  

Cell F13 in the Workbook2 has been linked to cell B5 in Workbook1. As well, B3: B12 in the Workbook2 has been linked to the range I2: I11 in Workbook1.

Consolidating Workbooks

When you consolidate workbooks, you are combining, condensing, and summarizing data from multiple sources into one destination. For example, a large company may have sales or profit data broken down into several different geographical locations and stored in different workbooks. Each workbook would have the same layout and structure (template), but the data in each will be different. With Excel, you can consolidate the different workbooks, summarizing and totaling the data in one place to get a unified view of your information.

To consolidate workbooks in Excel, first, open the workbooks, and then choose a destination workbook where the data will be consolidated. It is a good idea to set up the destination worksheet to be just like the worksheets that will supply the data so that the destination worksheet has the same labels and uses the same layout and cell locations for the same types of data. (This is not mandatory, but it can make the consolidation process a lot easier.)

Below we’ll explain a few ways to consolidate data:

• Consolidate by position: Use this method when the data from multiple source areas are arranged in the same order and uses the same row and column labels. For example, when you have a series of expense worksheets that are created from the same template.
• Consolidate by category: Use this method when the data from multiple source areas are arranged differently, but the same row and column labels are used. For example, you can use this method when you have a series of inventory worksheets for each month that uses the same layout, but each worksheet contains different items or a different number of items

Below you can see three open workbooks: destination workbook, WorkBookSource1, and WorkBookSource2. Notice that all three workbooks have a parallel structure in terms of columns, rows, and labels:

• To consolidate the workbooks, we will select cell B2 in DestinationWorkbook (the workbook with the empty cells) and choose Data → Consolidate:
  
• This will display the Consolidate dialog:
  

The Function menu contains a variety of functions that you can use to combine data. In this example, the default SUM function is used.

The Reference box is where you enter the ranges of data to be consolidated. You can enter the ranges manually or by selecting them from the different workbooks with your mouse or keyboard. (The latter method is recommended as it is much easier.) Once a range is entered, click Add to add it to the “All references” box. This field will contain all the workbook cell references that are to be consolidated.

For this example, we will click the Reference box and select B2: E14 from WorkbookSource1. This range will be displayed in the Reference field in the Consolidate box. 

• Clicking Add button will add it to the “All references” box. We will then select the same range from WorkBookSource2 and click Add. The Consolidate dialog now looks like this:

• At the bottom of the dialog, you will notice the phrase “Use labels in,” with two checkboxes below it.
• You can check these boxes to consolidate sheets based solely on row labels or column labels. That means that data in columns or rows that have the same labels will be consolidated even if the labels are under different column letters or row numbers in the source workbooks. (This is why consolidation can be is easier when all the worksheets involved have the same labels and design layout!) For this example, the checkboxes are empty so the consolidation will be based on the actual cell locations.
• If you check the “Create links to source data” box, the information in the destination workbook will be updated if the cell content in the contributing source workbooks is changed (like linking the workbooks).
• When we click OK, the source workbook data will be consolidated and combined into the destination workbook:
  

Combining Worksheets

The methods for combining worksheets belonging to the same workbook are very similar to the methods for combining different workbooks. You can link data between worksheets, or you can consolidate worksheets.

• To link cells in worksheets belonging to the same workbook, select the source cells, right-click, and click Copy:
  
• Next, right-click on a cell in the destination worksheet (preferably at the upper left of the destination range) and click Paste Link or Paste Special:
  
• (If you click Paste Special, the Paste Special dialog box will appear. When it does, click Paste Link. If you click the Paste Link command directly, you don’t have to do anything else.) The linked data will now appear in the destination worksheet at the cell or range you specified.

Linking cells is one way of combining worksheet data. You can also consolidate multiple worksheets belonging to the same workbook. Consolidating worksheets in the same workbook is a lot like consolidating multiple different workbooks.

• First, set up your worksheets so they all have a similar layout. 
• Next, select a cell at the upper left of the destination range and click Data → Consolidate.
  
• In the Consolidate dialog, click in the Reference box, and then select a range of cells from another worksheet in the workbook. Click Add to add it to the “All references” box:
  
• Add any additional references as necessary. If you want to link the data in addition to consolidating it (so that it is updated when changes are made to the source cells), select the “Create links to source data” box:
  

Once you are ready, click OK. Your worksheets will now be consolidated in the destination range.