Consolidate Function In Excel

Consolidate is an inbuilt function in Excel used to consolidate data from different workbooks opened simultaneously. This function is present in the "Data" tab. It allows us to select multiple data from other workbooks and consolidate them in a final workbook.

For example, suppose we have a whole year’s monthly data set of a company's sales, income, and profit in a separate Excel workbook. If each sheet has the same data arrangement in rows and columns, then using the "Consolidate" tool, we can sum up the whole year's data in a single Excel worksheet.

There is no inbuilt consolidate function in Excel. To insert consolidate function in Excel, we need to select Data - Data Tools - Consolidate.

Then, we can select the appropriate option.

We must follow the below steps.

1. First, select the required cell in the Consolidate Sheet.
2. Then, select the Data ribbon and click on Consolidate.
3. Now, the Consolidate window appears.
4. In the drop-down list, we must select the desired function based on the type of consolidation we need to perform.
5. Next, enter and select the references, and click on Add. It will add our chosen references to all references.
6. Remember, to click on Add to add the reference.

7. Now, we are done with the reference process. Click on OK to get the consolidated summary in the Consolidate Sheet.

   If you are interested in knowing more about the subject and learn into the more complicated aspects you can use a detail-oriented approach with the help of this Excel All In One Bundle Course.

Example #1: Consolidate Multiple Worksheets Into One

Before we explain the consolidating function in Excel, we would like you to download the workbook from the above link to practice along with me to learn this feature.

Assume we have received data from three worksheets in the same workbook, as shown below.

We have sales data for four zones on three different sheets.

Now we need to collate this data into a single worksheet. We have created a template named "Consolidated Sheet" for this in a separate sheet.

We must follow the below steps.

1. We must first select cell B2 in the "Consolidate Sheet."

   
   

2. Then, go to the "Data" ribbon and click on "Consolidate."

   
   

3. Once we click on "Consolidate," we will get the "Consolidate" window as shown below.

   
   

4. The first item in the window above is Excel's "Function" drop-down list. This drop-down list can select any function to consolidate the data.

   
   Based on the type of consolidation we need to perform, we must select the desired function. In this example, we want to summarize the total sales of three different worksheets. So we will choose the SUM function in excel, which is the default.
   
   

5. Next, we need to add the reference of each worksheet sale number cell. Then, click on the "Reference" box and select the sales number in the worksheet "File 1."

   
   

6. Once the reference is selected, click on "Add." As a result, it will add your chosen references to all references.

   
   

7. By placing a cursor back to reference, we will go to the second worksheet, "File 2."

   
   

8. It will automatically select the range of cells we set in the previous worksheet. But, again, we must click on "Add" to add the reference.

   
   

9. Finally, we will repeat the same step for the "File 3" worksheet.

   
   

10.  Now, we are done with the reference process. Click on "OK" to get the consolidated summary in the "Consolidate Sheet."

    
    

Wow is the word to describe, isn't it??

It is cool if we get the breakup of all the numbers and the total. So yes, we know what you need now. So let us create that as well.

Example #2: Dynamic Consolidate With Links To Source Data

How cool it is if we can create a link to the source whenever we make some changes in the source sheet. It shall reflect in the summary sheet instantly.

Below is the example screenshot of the same.

As we can see, some rows are grouped, which can be expanded by clicking on the "plus" button. So, Zone "E" has three different worksheet numbers. Then, in cell B5, we have a summary of all the three worksheet numbers.

To create a link while giving a reference, we need to check the box "Create links to source data."

It will create a link to the sourced data cells. Now we can see this in the cell references.

Example #3 – Consolidate Data to Blank Worksheet

In the above example, we created a template in the "Consolidate Sheet" worksheet and consolidated the data. But, of course, we can also consolidate without creating the template.

First, we must add a blank sheet, then follow the same procedure as above. But while giving references, we need to select the entire data range.

We must select the complete data range instead of only the numbers column.

Then we must repeat the same action for all the three worksheets.

Once the reference is given, we need to click on the checkboxes of "Top Row" and "Left Column" to create labels.

Once this is given, click on "OK" on the blank worksheet. It will create a consolidated summary like the one below.

Like this, we can use the Excel consolidate function to consolidate the data from different worksheets.

• Under consolidate, the option ranges of all the worksheets should be the same across worksheets.
• We can use any function in the consolidate option.