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.

Table of contents

• What Is Excel Consolidate Function?
  • How To Use Consolidate In Excel? (With Steps)
  • Examples
  • Important Things To Note
  • Frequently Asked Questions (FAQs)
  • Recommended Articles
• Consolidate function in Excel consolidates the data from multiple worksheets and provides desirable results.
• It is an inbuilt function in Excel, and we can insert a consolidated table by clicking on Data → Consolidate under the Data Tools group.
• We can get consolidated data and find the SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT Numbers, StdDev, Std Devp, Var, and Varp.
• While using consolidate function in Excel, we have to make sure that the labels are the same in all the worksheets we want to consolidate.

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.

1. What is the consolidate function in Excel?

In Excel, consolidate function is an inbuilt function. As the name suggests, the consolidate function consolidates data from different workbooks and provides the required results.

For example, the below image shows mars obtained by students in 3 different tests. Now, to find the total marks obtained by the students in all 3 tests, we can use the consolidate function in Excel.



The steps to find the SUM of all 3 test scores are:

• Step 1: First, create a new sheet and insert column A data. In this example, we are selecting Sheet 4.

• Step 2: Next, select cell B2 in sheet 4 and click on Data - Consolidate Function under Data Tools.

• Step 3: Select SUM and insert the cell range as a reference from all 3 sheets and select OK.



We can see the consolidated marks obtained by the students.
Likewise, we can use the consolidate function in Excel.

2. What are the functions available under consolidate function in Excel?

In Excel, using the consolidate function, we can find the consolidated data with SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT Numbers, StdDev, Std Devp, Var, and Varp functions.

3. Will Excel consolidate function work if the labels are different?

Consolidate function in Excel works well even if the labels are different in the left column. But we have to make sure that the labels are the same in each table. Also, it is important to avoid spelling mistakes or slight variations in the label as Excel may treat them as separate labels.

This article is a guide to Consolidate in Excel. We discussed using the consolidate function to consolidate data from different worksheets, examples, and a downloadable Excel template. You can learn more about Excel from the following articles: –

• Subtract Date In Excel
• Wildcard in Excel
• Turn On/Off Excel Scroll Lock