Consolidate Data – In Excel Files – Create Summary Report

Excel Consolidate Data Function To Summarize

This option enables user to consolidate in Excel. Summarize data from different worksheets to one sheet.

Data from different sheets with identical field headers will be summarized & displayed in the destination sheet. It is bit more advanced to the Pivot Table option.

How is this a better option? Lets see more…

How To Creating Consolidated Summary Report in Excel?

To summarize the data from different sheets into one, just follow these steps.

1. Click on destination cell.
2. Choose ‘Data’ from Menu
3. Select ‘Consolidate’ from ‘Data Tools’ section.
4. Choose ‘Sum’ in functions.
5. Select data from Sheet1 & Click Add
6. Select data from Sheet2 & Click Add
7. Select ‘Top row’ checkbox
8. Click ‘Ok’

Now, Excel will consolidate data from 2 sheets & display the summary in the destination cell selected in step1. You can see that matching row headers are all summed up with corresponding column headers.

Lets see this with an example:

How Does Data Consolidate in Excel Work?

Assume there is an Excel workbook that has sales data from different dates recorded for each sales representative. Here is an example data given below.

Each sheet has Employee name, Product name ,#Units Sold, Sales amount. We need to consolidate this data that was recorded for three days.

To manually do this, you need to copy data from each data & do a addition for each person with a pivot table or thru macro.

To consolidate these data from two different sheets, we cannot use Pivot tables directly. Just follow these simple steps.

Excel VBA Data Consolidate

The below is a VBA code obtained by recording Excel Macro while creating the Consolidate Report. This can be used in case if you want to automate the consolidation process.

Sub Consolidate_Data_In_Excel_VBA_Macro()
    Selection.Consolidate _
        Sources:=Array _
        ("'D:/FilePath/[Consolidate In Excel.xlsx]Sheet1'!R2C1:R5C5", _
        "'D:/FilePath/[Consolidate In Excel.xlsx]Sheet1'!R7C1:R10C5", _
        "'D:/FilePath/[Consolidate In Excel.xlsx]Sheet1'!R12C1:R15C5"), _
        Function:=xlSum, _
        TopRow:=True, _
        LeftColumn:=True, _
        CreateLinks:=False
End Sub

Modify the path of each sheet in Array, before executing this macro.

The consolidated summary will be created in the cell which is in current selection before executing this code. The only disadvantage of this option when compared to Pivot table is that: this option does not have that column headers drag & drop customization.

This is just a light weight & quick build consolidation report. Also, this option does not hold much memory & is very fast in creating the summary reports.