EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tools Consolidation in Excel
 

Consolidation in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated June 9, 2023

Consolidation in Excel

 

 

Excel Consolidation (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • Introduction to Consolidation in Excel
  • How to Consolidate Data in Multiple Worksheets?

Consolidate Data in Excel

Consolidate in Excel combines the data of more than 2 workbooks in the Data menu tab under the Data tools section with the name Consolidate. For this, we must have the same data type in different workbooks. Although different data sets will also work, there will not be proper alignment in consolidated data. Choose any mathematical function which we want to execute at last. Then select all the data using references from all the workbooks and click OK. This will combine the selected tables with the execution of the chosen mathematical function at the end.

How to Consolidate Data in Multiple Worksheets?

Let’s understand how to consolidate data in multiple worksheets with a few examples.

Example #1 – Consolidate Data in the Same Workbook

Let’s say we have a worksheet with sales data with four worksheets marked as per their region as North, South, East, and West. Now we want to consolidate the data into one place rather than in a sheet within the same workbook. There is a fifth sheet named consolidated file.

This example will show the consolidated sales for all the regions. First, here are the sample data files. You can see the worksheet names and the last consolidated file we must work on here.

Consolidation in Excel 1-1

Consolidation in Excel 1-2

Consolidation in Excel 1-3

Consolidation in Excel 1-4

This is our template in the “consolidated file” sheet, and now we will start consolidating data from the worksheets.

Consolidated File

We will now click on cell B3.

Consolidated File 2

We want the “Consolidate” function to insert the data from other sheets. As we can see above, cell B3 is selected, and now we will move up to the Data tab in Excel Ribbon and go to Consolidate.

Data Tools

Once we click on Consolidate, the below window will appear:

Consolidate window

The first thing we look at is the “Function” dropdown which shows many arithmetic functions like sum, count, max, average, etc. Since we want a sum of sales, we will select “Sum” in the dropdown. Now, we will go to the reference tab to add a reference to our data from different worksheets. We will then go to our first sheet containing North’s sales data. We only have to select sales data and not heading and rows. This is shown below.

Consolidation in excel 2-3

The reference is shown in the “Reference” box like this.

Consolidation in excel 2-4

Click “Add,” and the reference will be added in the “All Reference “box.

Consolidation in excel 2-5

Similarly, we will now add a reference from all other sheets like North, South, East, and West. Once we have finished adding the references, click “OK”.

Consolidation in excel 2-6

Now we have consolidated data for sales for the executives month-wise, at a national level.

Total Sales

Example #2 – Consolidate Yearly Sales Product Wise

In this, we have quarter wise sales for products A, B, C, and D, and we want consolidated yearly sales product-wise.

We will now go to the reference tab to add references to our data from different worksheets. Here we have data in four sheets; the first sheet with sales data for Q1 next has data for Q2, followed by data for Q3 and Q4. We will go to our first sheet, which contains the sales data for Q1. We will select the data as shown below.

Q1 Sales

Q2 sales

Q3 Sales

Q4 sales

Now, we will go to the Data tab and click Consolidate, and the below window will appear.

Data Tools

Consolidation in excel 2-2

We will click “Add,” and the reference will be added in the “All reference “box.

Consolidate 1

We will click “Add,” and the reference will be added in the “All reference “box.

Consolidate 2

We will click “Add,” and the reference will be added in the “All reference “box.

Consolidate 3

Below is our template for the consolidated datasheet. We will now select cell B2 to get the total sales data from other sheets.

Yearly Sales 1

We will select the “Sum “function from the drop-down. Likewise, we will add references from sheets Q2, Q3, and Q4. It will appear like this. All the references from all the sheets are now added. Click “OK”

Consolidate 4

We now have our consolidated yearly sales data with the sum totals for each product.

Yearly Sales

Taking our previous sample data, we will do the consolidation below. It is also possible to insert the consolidated table into a blank worksheet instead of creating a template table.

We don’t have a template for the consolidation table, and we want to get consolidated data in a blank worksheet with row and column labels. We will add a new worksheet; in our case, it is a “Consolidated file”.

Like before, we will go to the Data tab<Consolidation. Select “Sum” from the drop-down list.

Consolidation in excel 3-2

We will now select the reference from our datasheets. And start this with the “North” sheet and will then proceed with “South”, “East”, and “West” sheets. We will select the data below, including row and column labels.

Consolidate reference

We will then add the reference in the “All references” box:

North 1

Now add all the references in the same way from all the datasheets.

excel consolidate

We will now check the “Top Row” and “Left Column” and press OK.

Consolidate excel

We now see consolidated sales data with row and column labels.

Consolidate excel 1

If you want to update the consolidated data when the individual sheet gets updated, click on the box “Create Links to create data”. If you’re going to update data manually, don’t check the box and click OK.

Create links to Source data

Check data manually

The cells in the consolidated table now contain the sum formula, as shown below. This makes it dynamic in nature.

consolidated table

This is a dynamic consolidation table.

Example #3 – Consolidating Data From Different Workbooks

Suppose we have data in different workbooks and want to consolidate it into a single workbook. This gives us great flexibility and ease. We can do this as well.

We have quarterly sales data for various products, say A, B, C, and D, in different workbooks, as shown below.

Q1

Q2

Q3

Q4

consolidate

Once all the workbooks are open, we will go to a new workbook and click on cell B3. Add the references from all the worksheets below.

Adding references

We have consolidated data from all the workbooks into a new workbook.

New workbook

Moreover, any changes in the data in any workbook will also get updated in the new consolidated data workbook.

So we have learned how to use the consolidation function in Excel with the help of examples. It is useful in merging or collecting data into one sheet from different worksheets or workbooks.

Things to Remember About Consolidation in Excel

  • Be careful in selecting reference data when checking the boxes for “Top Row” and “Left Column”. You must then select the complete data, including the row and column labels.
  • When you are consolidating data of dynamic nature from different worksheets and workbooks, check the “create links to source data”, which will automatically update the changes in the data if done.

Recommended Articles

This is a guide to Consolidation in Excel. Here we discuss How to Consolidate Data in Multiple worksheets, practical examples, and a downloadable Excel template. You can also go through our other related articles –

  1. Advanced Formulas in Excel
  2. Column Header in Excel
  3. Autofit Row Height in Excel
  4. Cell References in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW