While working with Excel, we are able to combine and summarize several worksheets into one master file through Excel Consolidate. The worksheets can be in the same workbook or in separate workbooks.
The below images show examples of three workbooks named Line1, Line2 and Line 3. Each file contains a list of outputs of three products for three weeks. Note that there are three products in each workbook (A, B and C) and the column headers are uniform: Week1, Week2 and Week3.
Figure 1. Sample data for consolidation: Line 1
Figure 2. Sample data for consolidation: Line 2
Note that for Line2 worksheet, there is a column in between the Product and Week1 column header.
Figure 3. Sample data for consolidation: Line 3
Note that for Line3 worksheet, the range is one row down and one column to the right as compared to the data for Line1.
When using Consolidate, the data range can vary from one worksheet to another. The important thing to ensure is that the worksheets must have the same row and column headers.
How to use Consolidate?
In order to combine worksheets from different workbooks and enable data consolidation, we follow these steps:
- Click the Data tab > Consolidate
Figure 4. Consolidate command button in Data tab
- The Consolidate dialog box will appear. Select Sum under Function.
- For Reference, click the button and select the range B2:E5 in the worksheet named Line1, then click Add.
Figure 5. Consolidate dialog box
We have now consolidated Line1 and added it to our reference list.
In order to add Line2 and Line3 as our reference, we follow the same steps in data consolidation. After adding the three workbooks into our consolidated list, we tick the following checkboxes:
- Top row
- Left column
- Create links to source data – this is especially useful when we want to automatically update the master worksheet and enable a linked consolidation with the source worksheet
Figure 6. Consolidate preview
We click OK and Excel will instantly create a summary of the three worksheets. We didn’t have to copy and paste any data, or transfer from one worksheet to another. At once, the sum of values from the three worksheets will be shown, while the raw data for each product from each worksheet is hidden.
Figure 7. Summary of consolidated data
We can click on the plus “+” signs to expand the view and show all summarized and consolidated data.
Figure 8. Output: Consolidated data with expanded view
We can further format the consolidated data to highlight the headers and subtotals.
Figure 9. Output: Customized consolidated data
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment