The Excel Consolidate function helps you summarize data from multiple worksheets into one organized report. Whether you manage monthly sales, departmental expenses, inventory, or management information reports, Excel Consolidate can simplify repetitive calculations and reduce manual reporting work.
In this complete tutorial, you will learn how to use Consolidate in Excel with practical examples. We will explore consolidation by position, consolidation by category, selecting different summary functions, and handling multiple Excel workbooks. You will also learn when Power Query or PivotTables may be better alternatives.
Consider a company operating branches in Mumbai, Nashik, and Pune. Each branch maintains a separate Excel worksheet containing monthly sales records. The management team needs a single summary showing total product sales across all branches.
A reporting executive could manually copy the figures into a new sheet and prepare formulas for every product. However, this method becomes difficult when more worksheets, products, and reporting periods are involved.
Excel Consolidate provides a structured alternative. Instead of adding each range manually, users can select the source worksheets, choose a calculation method, and generate a combined summary. This is especially useful for recurring financial reports, branch summaries, budgets, and operational performance reviews.
The Consolidate command is available under Data → Data Tools → Consolidate in supported desktop versions of Microsoft Excel. It uses selected source ranges to produce a summarized report in a destination worksheet.
You can select Sum to calculate combined values, Average to calculate the mean of numeric entries, Count to count numeric values, or Max and Min to identify the highest and lowest values.
The Excel Consolidate function supports two important methods: matching information by cell position and matching information by category labels. Understanding the difference is essential for producing accurate reports.
Consolidation by position works when all source worksheets use the same layout, with equivalent values in corresponding cells.
For example, if every department maintains January expenses in column B and February expenses in column C, Excel can sum values based on their positions.
This method is suitable for standardized monthly templates and departmental budgets.
Category-based consolidation is useful when worksheets contain matching labels but their rows are not arranged in the same order.
For example, Laptop may appear in row 2 in one branch report and row 5 in another. By using the Left column label option, Excel can group matching product names.
Ensure that category names are consistent across all worksheets before consolidation.
Excel can also summarize information stored across separate workbook files.
For example, Sales, Finance, and Operations departments may submit individual Excel files containing budget information. Their ranges can be referenced in a master consolidation workbook.
When using external workbooks, maintain valid file paths and review links if source files are renamed or moved.
Choose the calculation based on the report you want to produce.
Sum: Total sales or expenses.
Average: Mean of numeric values.
Count: Number of numeric entries.
Max: Highest recorded value.
Min: Lowest recorded value.
For example, Sum is appropriate when calculating company-wide revenue from individual branch sales figures.
Suppose Branch A records sales of ₹1,50,000 and Branch B records sales of ₹1,25,000 for the same reporting month. Management wants the combined sales amount without preparing a separate manual addition for each source report.
Calculation: ₹1,50,000 + ₹1,25,000 = ₹2,75,000. By selecting Sum during consolidation, Excel can calculate this combined amount from the corresponding source ranges.
In the next section, we will extend this concept to a product-wise sales report. You will prepare two worksheets, configure the Consolidate dialog, and compare the output with manually verified totals.
Accurate consolidation begins with well-organized source information. Before using the feature, review the following requirements.
Excel Consolidate is best suited to summarizing numeric information from existing worksheet ranges. It provides a convenient method for preparing totals, averages, and other aggregated values.
Power Query is better suited to importing, cleaning, transforming, and appending detailed transaction records from different sources. For example, if you need every original invoice row rather than a summarized amount, Power Query is usually a more appropriate tool.
Important: Consolidation does not automatically append every source transaction into one detailed table. Additionally, the Create links to source data option can create linked consolidation results, but changes to source range structures may require updating the references.
After consolidating your Excel data, learn how to present summarized results through charts, KPIs, and interactive reports.
How to Create Excel Dashboards — Step-by-Step GuideFor additional technical guidance, you can also refer to Microsoft's official documentation on consolidating data across worksheets . The documentation covers consolidation by position, category, and linked sources.
The Excel Consolidate function makes it possible to summarize information from multiple worksheets without creating separate formulas for every source range. In this practical tutorial, you will learn how to combine branch-wise sales data into one accurate consolidated report.
We will use a realistic business reporting scenario involving two sales branches. Each branch maintains its own worksheet, and management needs one combined product-wise sales summary. You will learn how to prepare the source data, configure Excel Consolidate, select category-based matching, and verify the final results.
Suppose a company operates two branches selling laptops, keyboards, and computer mice. The sales team maintains separate Excel files or worksheets for each branch.
At the end of the reporting period, management wants to know the total sales value for each product across both branches. Instead of preparing multiple SUMIF formulas, the reporting executive can use Excel Consolidate by category.
In our example, the product names appear in a different order in the two worksheets. Category-based consolidation is therefore more appropriate than consolidation by position.
Open a new Excel workbook and create three worksheets with the following names: Branch A, Branch B, and Summary.
Enter the sample information shown below in cells A1:B4 on the Branch A and Branch B worksheets. The first row contains the column headers, followed by three product records.
Source data range: A1:B4
| Product | Sales (₹) |
|---|---|
| Laptop | 35,000 |
| Mouse | 9,000 |
| Keyboard | 4,000 |
Source data range: A1:B4
| Product | Sales (₹) |
|---|---|
| Keyboard | 7,000 |
| Laptop | 22,000 |
| Mouse | 8,000 |
The branch totals shown beneath the tables are calculated for explanation. Do not enter them inside the source ranges A1:B4, because the consolidation should include only the three product rows.
Notice that Laptop appears first in Branch A but second in Branch B. The Excel Consolidate function can still calculate the correct product totals when matching category labels.
Now that the source information is ready, follow these steps to configure the Excel Consolidate dialog. We will choose Sum as the calculation function and use the source column labels for matching.
Select the blank worksheet named Summary. Click cell A1, where the consolidated report should begin.
Make sure the destination area is empty. Excel needs sufficient space to display the summary results.
From the Excel Ribbon, select the Data tab. In the Data Tools group, click Consolidate.
The dialog box contains options for Function, Reference, All references, Use labels in, and Create links to source data.
Open the Function dropdown and select Sum.
This instructs Excel to add matching product sales values together. For example, Laptop sales from both branches will be added to calculate the combined amount.
Other functions, including Average, Count, Maximum, and Minimum, can be used for different reporting needs.
Click the Reference input box. Select the Branch A worksheet and highlight cells A1:B4.
The reference should look like:
'Branch A'!$A$1:$B$4
Click Add. The reference will be displayed in the All references list.
Repeat the same procedure for the Branch B worksheet. Select cells A1:B4 and click Add.
'Branch B'!$A$1:$B$4
You should now see both source ranges listed in the All references section.
Both ranges include the header row and product-name column. These labels are needed for category-based consolidation.
Under Use labels in, enable both of these options:
The Top row option identifies column headings such as Sales.
The Left column option matches product labels such as Laptop, Mouse, and Keyboard.
This allows Excel to summarize matching categories even when they appear in different row positions.
For this initial example, leave Create links to source data unchecked.
This creates a static summary using the selected source values.
If you want linked consolidation results that can reflect changes in existing source values, the Create links option can be used where supported.
Check that Sum is selected, both source ranges have been added, and the appropriate label options are enabled.
Click OK.
Excel now calculates the combined product sales values and displays the results in the Summary worksheet.
After completing the previous steps, your Summary worksheet should contain the following product-wise sales totals. The product order may differ, but the calculated values should match.
This report summarizes sales from Branch A and Branch B.
| Product | Total Sales (₹) |
|---|---|
| Laptop | 57,000 |
| Mouse | 17,000 |
| Keyboard | 11,000 |
The grand total shown above is calculated separately by adding the three consolidated product amounts.
Whenever data is combined from multiple worksheets, it is important to verify the output rather than assuming the consolidation is correct.
In our example, the Excel Consolidate function should generate these totals:
Laptop Sales: 35,000 + 22,000 = 57,000
Mouse Sales: 9,000 + 8,000 = 17,000
Keyboard Sales: 4,000 + 7,000 = 11,000
Grand Total: 57,000 + 17,000 + 11,000 = ₹85,000
We can also verify the combined sales amount using a standard Excel SUM formula.
=SUM('Branch A'!B2:B4,'Branch B'!B2:B4)
Enter this formula in an unused cell. The expected result is 85000. This matches the combined value from the Consolidate output.
In addition to checking the grand total, you can validate individual product values using the SUMIF function.
For example, to calculate combined Laptop sales from both branches, enter the following formula:
=SUMIF('Branch A'!A2:A4,"Laptop",'Branch A'!B2:B4)+SUMIF('Branch B'!A2:A4,"Laptop",'Branch B'!B2:B4)
The first SUMIF calculates Laptop sales from Branch A. The second SUMIF calculates Laptop sales from Branch B.
Adding the two results produces 57000. This should match the Laptop amount in the consolidated report.
Important reporting tip: Do not include existing subtotal or grand-total rows inside the source ranges. Including those figures may produce inflated consolidated results. Also check that product labels match consistently across worksheets.
This consolidation method can be used by sales executives, MIS professionals, accountants, and department managers who regularly prepare numerical summaries from multiple Excel worksheets.
Instead of manually calculating product totals, the Excel Consolidate function provides a consistent process for preparing aggregated reports. The same approach can be adapted for branch expenses, regional budgets, monthly production summaries, and inventory valuation.
For large transaction datasets requiring repeated data cleaning, detailed row-level analysis, or frequent changes to source structures, Power Query or Power BI may be more suitable alternatives.
In the next section, you will explore advanced consolidation, common mistakes, source linking, Power Query comparisons, and frequently asked questions.
Explore Advanced Excel Consolidate Examples →After learning category-based consolidation, it is important to understand how the feature behaves with identical layouts, changing data, external workbooks, and different summary functions.
Suppose two departments prepare monthly expense reports using identical templates. Every worksheet has the same expense categories in the same order.
The following example contains Rent and Travel expenses for January and February.
| Expense | Department A Jan | Department A Feb | Department B Jan | Department B Feb |
|---|---|---|---|---|
| Rent | 12,000 | 12,000 | 8,000 | 8,000 |
| Travel | 3,000 | 4,500 | 2,000 | 1,000 |
Create one worksheet for Department A and another for Department B. Use column A for Expense, B for January, and C for February. Both worksheets must have the same row order.
Enter Rent in row 2 and Travel in row 3.
For this position-based example, select only
the numeric ranges B2:C3
from both worksheets.
Open Data → Consolidate, select Sum, add both numeric ranges, and leave Top row and Left column unchecked. Start the output in a blank cell, such as Summary!B2.
Excel sums corresponding positions. Because labels were not included in the selected ranges, add the January, February, Rent, and Travel headings manually around the output.
Rent January = 12,000 + 8,000 =
20,000
Rent February = 12,000 + 8,000 =
20,000
Travel January = 3,000 + 2,000 =
5,000
Travel February = 4,500 + 1,000 =
5,500
Combined expenses across both months: 50,500
This example works because the source layouts are identical. If Rent and Travel appear in different rows, category-based consolidation is the safer option.
Excel offers a checkbox called Create links to source data. When enabled, Excel creates linked consolidation results that can reflect changes in existing source values.
For example, suppose Branch A's Laptop sales change from ₹35,000 to ₹40,000. A correctly linked consolidation can reflect the revised total of ₹62,000 for Laptop.
However, linked consolidation does not mean that every future structural change is handled automatically. Adding new rows, replacing worksheets, or changing reference ranges may require you to update the consolidation setup.
Check whether you accidentally included total rows, duplicated source ranges, or selected the wrong summary function. Recalculate the source totals independently.
Product names must match consistently. For example, "Laptop" and "Laptop-01" will not be treated as the same category. Standardize labels before consolidation.
A fixed range such as A1:B4 excludes rows added outside that range. Extend the references and repeat or update the consolidation when needed.
The feature is available in supported desktop Excel versions. Availability may differ on Excel for the web or other platforms. Use a supported desktop application.
Values imported as text may not behave like numeric sales values. Convert them to valid numbers and check the data types before running Sum.
External references may become invalid after a source file is renamed or moved. Review the workbook links and update the reference paths as necessary.
| Feature | Best Use | Important Detail |
|---|---|---|
| Excel Consolidate | Summarizing values across ranges | Good for simple Sum, Average, Count and category-based summaries. |
| Power Query | Combining and cleaning data | Can import, transform and append detailed rows with a refresh process. |
| PivotTable | Interactive data analysis | Supports grouping, filtering, rearranging and reporting on source data. |
| VSTACK | Stacking worksheet ranges | Available in supported modern Excel versions and designed to stack arrays. |
Choose Excel Consolidate when you need a quick numerical summary. Choose Power Query when you want a repeatable process for importing, cleaning, and combining detailed data. Choose PivotTables when users need to explore and filter the resulting dataset.
It summarizes numeric values from multiple worksheets or ranges into one report using functions such as Sum, Average, and Count.
Yes. You can add references to ranges stored in different Excel workbooks. Maintain valid file locations and verify external links.
It is not designed to concatenate text. TEXTJOIN can combine text from multiple cells, while Power Query can append rows containing both text and numeric columns.
Category-based consolidation relies on matching labels. Inconsistent names can produce separate result categories. Standardize the labels first.
No. It aggregates numeric values and is not a record-level deduplication tool. Use Remove Duplicates or Power Query when you need to eliminate duplicate transactions.
Yes, when the source data is structured with corresponding month labels or aligned ranges. For transaction-level grouping by date, Power Query or PivotTables are generally more flexible.
Yes. Microsoft documents the feature for Excel 2016, 2019, 2021, 2024, and supported Microsoft 365 desktop versions.
Sum adds numeric values. Average calculates the average of the included numeric entries. For example, 35,000 and 22,000 produce a Sum of 57,000 and an Average of 28,500. That is an unweighted average of the two included amounts.
Excel Consolidate is a useful tool for anyone preparing summary reports from several worksheets. It is especially helpful when departments, branches, or reporting periods follow a consistent reporting structure.
By understanding the difference between consolidation by position and by category, selecting appropriate functions, and validating the result, you can prepare more reliable management reports.
For larger recurring reporting requirements, consider Power Query or Power BI to create more flexible data preparation and analysis workflows.
Strengthen your Excel knowledge with these short video lessons. Watch the demonstrations, explore the techniques, and practise the concepts directly in Microsoft Excel.
Continue exploring practical Excel functions, formulas, reporting techniques, and automation tutorials on TechBikash28.
Prefer learning through visuals? Explore these short, illustrated Excel learning guides from TechBikash28. Discover practical techniques for data consolidation, Excel formulas, dashboards, and everyday reporting.
