EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Consolidation in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Consolidation in Excel

By Madhuri ThakurMadhuri Thakur

Consolidation in Excel

Excel Consolidation (Table of Contents)

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

Consolidate Data in Excel

Consolidate in Excel is used to combine the data of more than 2 workbooks which is available in the Data menu tab under the Data tools section with the name Consolidate. For this, we must have the same type of data 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 on 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 in which we have sales data with four worksheets marked as per their region as North, South, East, and West. Now we would like to consolidate the data into one place rather than in a sheet within the same workbook. There is a fifth sheet named consolidated file.

Start Your Free Excel Course

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

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)

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

Where 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 will 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 that contains the sales data for North. 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

In the same manner, we now will 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.

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 now go to the reference tab to add references to our data from different worksheets. We will go to our first sheet that 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

In case if we want to insert the consolidated table in a blank worksheet instead of making a template table. We can do that as well. Taking our previous sample data, we will do the consolidation below.

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”.

Now, 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. We will start this with the “North” sheet and will then proceed with “South”, “East”, and “West” sheets. We will select the data as shown below, which includes 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 both the “Top Row” and “Left Column” and then 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 want to update data manually, then 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

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

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

Q1

Q2

Q3

Q4

consolidate

Once we have all the workbooks 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 workbooks 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 a very useful function 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 you are checking the boxes for “Top Row” and “Left Column”. You will then have to 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 along with 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
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more