How to Summarize Data in Excel Using Consolidate Command

Suppose you have a bunch of Excel data that are totally similar but needs to be created individually for some valid reasons. A good example is a purchase order. These POs can be created in individual sheets within one workbook, or spread across various workbooks. And in the course of time, you might want to collate the summary information to see how many of which inventory are moving in your business.

The easiest way to collate such data or information is to use the Consolidate feature of Excel. Here are the steps:

Step 1: Open your MS Excel program

Step 2: You need to open ALL of the Excel files that you need to consolidate. In this sample case, I will just consolidate the contents of the various sheets in ONE file so that I can give the download link for that file below. But you can do the consolidation thing among various files and sheets.

NOTE: 

In my case, as you can see in my sample file, I want to consolidate the ORDERS sheet. 
I made 5 order sheets and it must be consolidated in the sheet called CONSOLIDATED. 
The order sheets have cool formulas that automated few tasks.

Step 3: You can build the consolidation in a separate workbook, or in ANY sheet in the excel files that you want consolidated. In my sample case, I created a “consolidated” sheet.

Step 4: In the sheet called “Consolidated” click on cell A1. This is where I intend to place the consolidated data.

Step 5: Then click on Data tab, then click on Consolidate command under Data Tools group

Step 6: For Function, select SUM

Step 7: For the reference, this is the tricky part. You need to click on the button with the small red arrow to minimize the dialog box and be able to select the Excel files you opened or the sheets that you want to include in the consolidate process.

Step 8: Just click on the sheet in the workbook. For this sample, I need to select Order 1 sheet. then highlight the whole table or range.

Step 9: When the reference is reflected in the Reference box, click Add. Then select the other sheets, highlight the same table again and then click Add button when the reference is reflected in the Reference box. Do these in all the sheets in the files that you want to consolidate.

Step 10: Then click to check the bottom check boxes.

Step 11: Then click OK.

Step 12: The consolidated table will then show.

You can download the sample Excel file below.