How to Combine Data from Multiple Sources in Microsoft Excel 2016

Consolidate multiple sets of data into a single workbook

When you create a series of worksheets that contain similar data, perhaps by using a template, you build a consistent set of workbooks in which data is stored in a predictable place. For example, consider a workbook template used to track the number of calls received from 9:00 A.M. to 10:00 P.M.

Consolidation targets should have labels but no data

Using links to bring data from one worksheet to another gives you a great deal of power to combine data from several sources into a single resource. For example, you can create a worksheet that lists the number of calls you receive during specific hours of the day, use links to draw the values from the worksheets in which the call counts were recorded, and then create a formula to perform calculations on the data. However, for large worksheets with hundreds of cells filled with data, creating links from every cell is a time-consuming process. Also, to calculate a sum or an average for the data, you would need to include links to cells in every workbook.

Fortunately, there is an easier way to combine data from multiple worksheets in a single worksheet. By using this process, called data consolidation, you can define ranges of cells from multiple worksheets and have Excel summarize the data. You define these ranges in the Consolidate dialog box.

Important

To consolidate data, every range included in the consolidation must be of the same shape and size.

Summarize data sets of the same shape by using consolidation

Cells that are in the same relative position in the ranges have their contents summarized together. When you consolidate the ranges, the cell in the upper-left corner of one range is added to the cell in the upper-left corner of every other range, even if those ranges are in different areas of the worksheet. After you choose the ranges to be used in your summary, you can choose the calculation to perform on the data. Excel sums the data by default, but you can select other functions to summarize the data.

Important

You can define only one data consolidation summary per workbook.

To consolidate cell ranges from multiple worksheets or workbooks

1. Open the workbook into which you want to consolidate your data and the workbooks supplying the data for the consolidated range.
2. In the workbook into which you want to consolidate your data, on the Data tab, in the Data Tools group, click Consolidate.

3. In the Consolidate dialog box, click the Collapse Dialog button at the right edge of the Reference field to collapse the dialog box.

   

   Clicking the Collapse Dialog button minimizes the Consolidate dialog box

4. On the View tab, in the Window group, click Switch Windows and then, in the list, click the first workbook that contains data you want to include.

5. Select the cell range, click the Expand Dialog button to restore the Consolidate dialog box to its full size, and click Add to add the selected range to the All references pane.

   

   Add data ranges to create a consolidation range

6. Repeat steps 3 through 5 to add additional ranges to the consolidation.
7. If you want to change the summary function, click the Function arrow in the Consolidate dialog box and select a new function from the list.
8. Click OK.