Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
While working in excel we have lots of data available in different worksheets and if we want to merge or consolidate data on a single worksheet excel does have a function to do so after opening all the worksheets which has data needs to be consolidated, Consolidate button is available in the data tools section of the Data tab.
Excel Consolidate Data (Table of Contents)
What is Consolidate Data in Excel?
If you are not aware of the Consolidate tool in excel this article will help you to understand the dynamics of the Consolidate tool in excel.
In common when we receive data from different people we get many worksheets to deal with. Consolidating everything is a herculean task especially for intermediate levelers. In my early part of a career, I got data from a broad range of sources.
For example, I was used to get sales data from 4 different zones from zonal heads. Four different zones include 4 different worksheets. At the end of the data, I was required to collate everything to one worksheet and create a database for analysis purpose. I was doing it manually the traditional way of copy & paste.
But I have spent some time learning new things and consolidate tool is one of them and it has helped me a lot to collate the data with minimal efforts.
Let us look at how this Consolidate Data Tool in Excel is used?
How to Consolidate Data in Excel?
Below is the example of Consolidate data in Excel.
I have sales data from four different regions in four different worksheets.
Each contains the same format and the same number of rows and columns. I need to consolidate all the sheet to single sheet named Summary.
I have created the same template as it is in the other 4 sheets.
After creating the above template in the sheet Summary, select the cell B2 where we want to consolidate all the 4 different regions.
Now go to Data > Consolidate
Once you have selected Consolidate option you will see the below window.
This is the window we need to do the magic. Firstly, we see the Function drop-down the list from this drop-down list we need to select our type of consolidation. Here we need to select SUM function because we are consolidating all the zone’s sales data and creating a summary report. So select SUM under this option.
Next step is to give reference to all the 4 different region’s sheet. Click on the reference box and go to the first sheet of all the zone’s sheet i.e. South. After selecting the range click Add button.
After Clicking on Add button it adds the references to all cell references.
Similarly, create an excel link for all the other regions sheets. Finally, it will look like this.
Now click on the OK button, your consolidated data is ready without any errors at all.
Now we have created a summary report this is not a dynamic report because we have not created a link to the other tables. If there are any changes tables remains the same and our report goes wrong in this case. We need to link the table to the other sheets to make the report dynamic.
In the consolidate dialogue box select the check the box Create links to source data. Click on OK.
Then it completes the process.
Now it has created SUM function. There is a plus (+) icon on the right-hand side of the spreadsheet, click on the plus (+) icon to see the breakup.
Other Options in Consolidate
- Function: This is the list of all the available function in Consolidate tool. You can use SUM, AVERAGE, COUNT, MAX, MIN function, etc
- Reference: This is where we need to select the ranges of different sheets to consolidate the data to a single sheet.
- Browse: This is the beauty of the consolidate option. Not only it consolidates from the same workbook but it can fetch the data from other workbooks as well.
- Add & Delete: This where we can add and delete all the references.
- Top Row: Use the top row as row headings
- Left Column: Use the left-hand side of the data as the column headings.
- Create Links to Source Data: This will make the report dynamic. Consolidate option create the formula for all the other sheets to make the report real dynamic.
VBA Code to Combine Different Workbook Data in Excel
Copy and paste the below VBA code to combine data from different opened workbooks.
'This code will collate data from different Workbooks and different Worksheets
Dim WB As Workbook
Dim Ws As Worksheet
Dim i As Long
i = 1
For Each WB In Workbooks
If WB.Name <> "Main Sheet.xlsm" Then
For Each Ws In Worksheets
ThisWorkbook.Activate 'Thisworkbook refers to Main Data WB
ActiveSheet.Name = Ws.Name 'this will rename the sheet as the original sheet name i.e copied sheet name
i = i + 1
Things to Remember About Consolidate Data in Excel
- VBA code can consolidate the data from all the opened workbooks only to different worksheets, not to the single sheet.
- Under consolidate, option ranges of all the worksheet should be same across worksheets.
- We can use any kind of functions in consolidate option.
This has been a guide to Consolidate Data in Excel. Here we discuss how to Consolidate Data from multiple excel worksheets into a single worksheet along with practical examples and downloadable excel template. You may learn more about excel from the following articles –