Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Excel Consolidate – Excelchat

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc