Excel Consolidate Function: 5 Practical Examples with Step-by-Step Instructions
When monthly reports arrive in separate Excel sheets, preparing one summary can become a surprisingly repetitive job. Someone has to check the headings, identify matching categories, add the values and make sure nothing has been counted twice. The Excel Consolidate function can handle much of that summarising work using a built-in Excel command.
In this tutorial, we'll work through a familiar business situation: two branches maintaining their sales figures separately. Then we'll go beyond the usual Sum example and explore Product (multiplication), Average, Count and Max. I'll show you which option to choose in the Consolidate dialog, what data to select, what Excel should return and how to check the answer yourself.
The goal is not simply to click a button and get a number. It's to understand what that number means. For example, an average of two branch totals is not automatically an average selling price, and Count does not mean the sum of transaction quantities. Those distinctions matter in real reports.
What you will learn
- Find the Consolidate command and understand every important setting
- Choose between consolidation by position and by category
- Prepare a sales summary from two worksheets using Sum
- Multiply sequential numeric factors using Product
- Calculate Average, Count and Max without misleading results
- Handle changing source data, mismatched labels and linked summaries
- Know when a PivotTable or Power Query is a better option
Software: A supported desktop version of Microsoft Excel. The example values are illustrative and are not actual client records.
What does the Excel Consolidate function actually do?
Consolidate takes numeric information from two or more selected ranges and calculates one summary using the function you choose. Those ranges may come from different worksheets in the same workbook or from separate Excel workbooks. You can choose Sum, Average, Count, Product, Max, Min and other statistical functions.
Let's say a company has separate monthly sales files for its Nashik and Pune branches. The product list is the same, but the products are not necessarily in the same row order. A manual approach involves looking for Laptop in each file, adding the two amounts, doing the same for Mouse and Keyboard, and repeating the process every month. Consolidate can match those product labels and prepare the combined summary.
What it does not do is append every original invoice row into a larger transaction list. That's a different task. If your objective is to retain all source rows, complete with invoice numbers, dates and descriptions, consider Power Query or the VSTACK function instead.
Good uses
Branch-wise sales totals, departmental expenses, budget comparisons, numeric inventory summaries and highest reported values across standard worksheets.
Not the best use
Combining individual invoice rows, removing duplicate transactions, joining unrelated tables or building a continuously changing source database.
Consolidate by position or by category?
Excel offers two approaches, and this choice is more important than it first appears. If the worksheets look identical, you can consolidate by position. If the information has the same category labels but a different order, consolidate by category instead. Picking the wrong method is one of the most common reasons for incorrect totals.
By position
Every worksheet follows the same template. January expenses are in B2, February expenses are in C2, and the next department uses those exact positions. Excel adds or otherwise combines corresponding cells.
Example: Two departments submit identical Rent and Travel expense templates.
By category
The product names are consistent, but Laptop may be in row 2 of one sheet and row 3 of another. Excel uses the labels in the top row or left column to identify matching categories.
Example: Two branches send product-wise sales in different row orders.
Label check: “Wireless Mouse” and “Mouse” are different labels. Consolidate will not know they mean the same item. Standardise names and units before calculating the report.
How to use Data → Consolidate, step by step
Before we open the first example, here is the exact workflow. Once you understand these settings, you can repeat the same steps for Sum, Product, Average, Count or Max. Only the function and, sometimes, the source ranges change.
Prepare the source worksheets
Put each data range on its own worksheet. Use clear labels in the first row and, for category-based consolidation, put the item names in the left column. Check that numeric cells really contain numbers rather than numbers stored as text.
Choose an empty destination
Open a blank sheet named Summary and select A1. The consolidated output will begin at the upper-left cell you choose, so leave room to its right and below.
Open the Consolidate dialog
On the Excel ribbon, choose Data → Data Tools → Consolidate. A dialog will appear with Function, Reference, All references and Use labels in. Depending on the platform and Excel version, the layout may look a little different.
Select the Function
For total sales, choose Sum. For multiplying factors, choose Product. For an average across numeric entries, choose Average. We'll show the remaining functions in their own examples below.
Add every source range
Click in Reference, select the first source range (for example 'Branch A'!$A$1:$B$4), and click Add. Repeat for the second range. Both references should appear in All references. If you accidentally add the same range twice, remove the duplicate before proceeding.
Tell Excel whether to match labels
For our product examples, tick Top row and Left column. If the data is in identical numeric positions with no category headers included in the selected ranges, leave these boxes unchecked.
Decide whether to create source links
For a simple first exercise, leave Create links to source data unchecked. If you want a linked summary that can respond to changes in existing source values, the option can be enabled when supported. Linked results and changing source structures are discussed later in this guide.
Click OK and validate the result
Excel produces the output in Summary. Format the result, compare it with a small manual calculation, and make sure no grand-total row was included in the original range. A good reporting process always includes this final check.
Dialog settings cheat sheet
| Setting | What it controls |
|---|---|
| Function | Sum, Product, Average, Count, Max or another calculation |
| Reference | One source range that you are adding |
| All references | The complete collection of source ranges |
| Top row | Match corresponding column headings |
| Left column | Match corresponding category or item labels |
| Create links to source data | Build a linked summary rather than only static output |
SUM: combine sales from two branches
Let's begin with the same sales example from our original Consolidate tutorial. Branch A and Branch B sell Laptops, Mice and Keyboards. Each branch has its own sheet. Management wants one product-level report and one combined sales total. Importantly, the product rows are in a different order across the two sheets.
Source 1: Branch A (A1:B4)
| Product | Sales (₹) |
|---|---|
| Laptop | 35,000 |
| Mouse | 9,000 |
| Keyboard | 4,000 |
Branch A total: ₹48,000. This total is for checking only; don't include it as another row in the A1:B4 source range.
Source 2: Branch B (A1:B4)
| Product | Sales (₹) |
|---|---|
| Keyboard | 7,000 |
| Laptop | 22,000 |
| Mouse | 8,000 |
Branch B total: ₹37,000. Notice that Keyboard is now the first record. If you consolidate by position, the results won't mean what you expect. We need to match product names.
Settings to enter in the Consolidate dialog
| Field | Choose / enter |
|---|---|
| Destination | Summary!A1 |
| Function | Sum |
| Reference 1 | 'Branch A'!$A$1:$B$4 |
| Reference 2 | 'Branch B'!$A$1:$B$4 |
| Use labels in | Top row ✓ Left column ✓ |
| Create links | Unchecked for this static example |
Click OK. Excel matches Laptop with Laptop, Mouse with Mouse and Keyboard with Keyboard, regardless of row order. You may see the output categories in a different order; the actual amounts are what matter.
Expected consolidated result
| Product | Branch A | Branch B | Consolidated SUM |
|---|---|---|---|
| Laptop | 35,000 | 22,000 | 57,000 |
| Mouse | 9,000 | 8,000 | 17,000 |
| Keyboard | 4,000 | 7,000 | 11,000 |
The arithmetic is straightforward: Laptop is 35,000 + 22,000 = 57,000; Mouse is 9,000 + 8,000 = 17,000; Keyboard is 4,000 + 7,000 = 11,000. The combined total is ₹85,000. The grand total is a separate calculation in this illustration, not an extra row that should be included in the source ranges.
Verify it with formulas
One useful habit is to check the summary against a formula that does not rely on the Consolidate dialog. For the total of both branches:
=SUM('Branch A'!B2:B4,'Branch B'!B2:B4)For Laptop alone, you can use SUMIF on each source and add the results:
=SUMIF('Branch A'!A2:A4,"Laptop",'Branch A'!B2:B4)+SUMIF('Branch B'!A2:A4,"Laptop",'Branch B'!B2:B4)Those formulas should return ₹85,000 and ₹57,000 respectively. If they don't match your Consolidate output, check the selected ranges and product labels before using the report.
PRODUCT: multiply values across worksheets
The Product option is different from Sum. Instead of adding corresponding numbers, it multiplies them. This is not a sensible way to combine branch sales amounts, but it can be useful when you're working with sequential factors, such as two successive adjustments applied to the same base value.
Imagine a costing analyst needs to combine two adjustment factors. One sheet holds the first factor; another holds the second. Multiplying 1.10 and 0.95 gives 1.045. That means applying a 10% increase followed by a 5% reduction results in a net 4.5% increase. The order matters conceptually, even though multiplication itself is commutative.
Worksheet: Factor_A (A1:B3)
| Scenario | Factor |
|---|---|
| Product A | 1.10 |
| Product B | 1.05 |
Worksheet: Factor_B (A1:B3)
| Scenario | Factor |
|---|---|
| Product B | 1.02 |
| Product A | 0.95 |
Exact Product settings
Start in a blank sheet named Product Summary at A1. Select Data → Consolidate. Set Function = Product. Add Factor_A!$A$1:$B$3 and Factor_B!$A$1:$B$3. Enable both Top row and Left column so Excel matches the product labels. Leave Create links unchecked, and click OK.
| Scenario | Full multiplication | Expected Product |
|---|---|---|
| Product A | 1.10 × 0.95 | 1.045 |
| Product B | 1.05 × 1.02 | 1.071 |
For a simple check, type =PRODUCT(Factor_A!B2,Factor_B!B3) in another cell. It returns 1.045 for Product A. If the original price were ₹1,000, applying those factors would give ₹1,000 × 1.045 = ₹1,045.
Keep the business meaning clear: Product multiplies values; it does not perform a weighted average or a total. Never use Product to combine two branch revenues just because you see a multiplication option in the dialog. Use it only when the underlying numbers are genuinely meant to be multiplied.
AVERAGE: find the mean branch value
Suppose you want to compare the typical amount reported by a branch for each product. Using the same Branch A and Branch B tables from Example 1, we can replace Sum with Average in the Consolidate dialog. This produces the arithmetic mean of the included numeric entries for each product.
Open a new blank worksheet called Average Summary, select A1, choose Data → Consolidate and set Function to Average. Add 'Branch A'!$A$1:$B$4 and 'Branch B'!$A$1:$B$4. Choose Top row and Left column. Click OK.
| Product | Calculation | Expected Average (₹) |
|---|---|---|
| Laptop | (35,000 + 22,000) ÷ 2 | 28,500 |
| Mouse | (9,000 + 8,000) ÷ 2 | 8,500 |
| Keyboard | (4,000 + 7,000) ÷ 2 | 5,500 |
For Laptop, Excel takes the two entered amounts, adds them and divides by two. Check it independently using =AVERAGE('Branch A'!B2,'Branch B'!B3). The answer should be ₹28,500.
There is an important distinction here. This is the average reported sales amount across branches. It is not the average selling price per laptop, because we have not included quantities sold. If one branch sells 10 units and another sells 100 units, the correct price analysis may need revenue divided by total quantity, not an unweighted average of branch prices.
COUNT: check how many numeric reports exist
Count answers a different question from Sum. It tells you how many numeric values were included for each matching category. You might use it to understand how many weekly sheets contain a recorded numeric value for a product. It doesn't add the product quantities, and it doesn't count an empty cell as zero-filled data.
For this example, prepare three worksheets named Week_1, Week_2 and Week_3. Each has the headings Product and Qty in A1:B1, with the three product names in rows 2 to 4. Leave the blank cells below genuinely empty; don't type a dash or the word 'blank'.
| Product | Week_1 | Week_2 | Week_3 |
|---|---|---|---|
| Laptop | 12 | 15 | 11 |
| Mouse | 10 | blank | 9 |
| Keyboard | 8 | 7 | blank |
Run the Count consolidation
Select Count in the Function dropdown. Add Week_1!$A$1:$B$4, Week_2!$A$1:$B$4 and Week_3!$A$1:$B$4. Select Top row and Left column, then click OK.
| Product | What Excel counts | Expected Count |
|---|---|---|
| Laptop | 12, 15, 11 | 3 |
| Mouse | 10, 9 | 2 |
| Keyboard | 8, 7 | 2 |
Mouse has two recorded numeric values because Week_2 is empty. The number 2 tells us the count of numeric entries, not that two mice were sold. If you need the quantity sold, choose Sum instead. This is a good example of why the selected function must match the question you're asking.
COUNT and COUNTA are different ideas: Count deals with numeric entries. A text marker such as “Pending” is not another quantity to count with this function. If the task is completeness checking across mixed text and numbers, use a purpose-built data-quality method instead.
MAX: identify the higher amount by product
Let's return to the branch sales data. A regional manager might want to see the higher amount reported by either branch for each product. We can do that with the Max option in the same Consolidate dialog. Max does not produce the combined revenue; it returns the highest of the numeric values supplied for each category.
Open Max Summary and select A1. Choose Data → Consolidate → Function Max. Add the Branch A and Branch B ranges, tick Top row and Left column, and click OK.
| Product | Branch A (₹) | Branch B (₹) | MAX (₹) |
|---|---|---|---|
| Laptop | 35,000 | 22,000 | 35,000 |
| Mouse | 9,000 | 8,000 | 9,000 |
| Keyboard | 4,000 | 7,000 | 7,000 |
The highest Laptop amount is ₹35,000, reported by Branch A. Keyboard is different: Branch B reported the higher amount of ₹7,000. If you instead select Min, Excel would return ₹22,000 for Laptop, ₹8,000 for Mouse and ₹4,000 for Keyboard.
Use Max to compare reported amounts, upper limits or peak values. To find which branch supplied a peak, you would still need to compare the source labels or build an analysis that also retrieves the branch name. Consolidate calculates the value, not a complete attribution report.
Consolidate by position: identical expense templates
All five worked examples above use matching labels. Sometimes you don't need category matching at all. Suppose two departments follow exactly the same expense template, with Rent in row 2, Travel in row 3, January in column B and February in column C. Because the layouts are identical, Excel can add corresponding numeric cells by position.
| Expense | Dept A Jan | Dept A Feb | Dept B Jan | Dept B Feb |
|---|---|---|---|---|
| Rent | 12,000 | 12,000 | 8,000 | 8,000 |
| Travel | 3,000 | 4,500 | 2,000 | 1,000 |
Put the departmental data on two sheets, with headings in A1:C1 and records in rows 2:3. On a blank Summary sheet, select B2. Set Function to Sum and add only the numeric ranges Dept_A!$B$2:$C$3 and Dept_B!$B$2:$C$3. Leave Top row and Left column unchecked, because we have deliberately excluded labels from the selected ranges.
| Expense | January total | February total |
|---|---|---|
| Rent | 20,000 | 20,000 |
| Travel | 5,000 | 5,500 |
Excel returns the numeric 2-by-2 output. Because we didn't include the labels, you need to write the Rent, Travel, January and February headings around the result yourself. Position-based consolidation works well only while the template stays consistent. If the departments rearrange rows, use category-based matching instead.
Can Excel Consolidate update when the source changes?
Yes, but the answer depends on how the report was created. When Create links to source data is unchecked, the Consolidate result is a static snapshot. If Branch A later changes Laptop sales from ₹35,000 to ₹40,000, that static summary will not automatically become ₹62,000. You'll need to run or update the consolidation again.
When Create links to source data is checked, Excel creates linked results that can reflect changes in existing source values. That can be helpful for an ongoing report. However, linked consolidation is not the same as a complete automation system. Adding new rows outside the referenced range, changing workbook names or replacing entire source structures may require new references.
Excel also cannot create source-data links when the source and destination areas are on the same worksheet. Keep the source ranges on separate sheets and be careful when moving externally linked workbooks.
Consolidating ranges from other workbooks
You can choose ranges from other Excel files as well. For example, three departments might email their monthly budget workbooks. Open or browse to the files, add each relevant range in the Reference field, and use the same label settings. Verify the file paths before saving the master report, particularly if people frequently move or rename the source files.
When your monthly process keeps changing: If new files arrive in a folder every week or month and you need to keep every original transaction, Power Query is usually a better long-term method. It can import, transform and append rows through a refreshable query.
Why is Consolidate showing the wrong result?
Most Consolidate problems can be traced to the source data or to one dialog setting. Before starting again from scratch, check the actual ranges, the function selected and whether the worksheet labels line up. Here are the mistakes I would look for first in a business report.
Different category names
“Mouse” and “Wireless Mouse” can be treated as separate labels. Standardise the names before rerunning the report.
A total row was included
If the source already contains a subtotal or grand total and you include that row, the report may double-count figures. Select the detailed range only.
Wrong function selected
Average and Sum answer different questions; Product multiplies rather than adds. Confirm the intended calculation first.
Incorrect method
Position-based consolidation will produce misleading answers when categories appear in different row orders. Use labels for category matching.
Text that looks like numbers
Imported values may be stored as text. Convert them to numeric data before applying Sum, Average or other numerical summaries.
New records outside the range
The old reference A1:B4 will not include a new row 5. Extend the reference and rerun or update the consolidation process.
For troubleshooting, I recommend calculating one category manually before checking the entire report. If Laptop should be 57,000 but Excel returns 35,000 or 22,000, the missing source range is often the problem. If it returns a figure that is far too high, check for repeated references or an included total row.
Consolidate vs Power Query, PivotTable and formulas
Excel has more than one way to bring information together. I wouldn't use Consolidate for every reporting problem. Its strength is quick numerical summarisation across known ranges. When the business question changes, another tool may become more suitable.
| Method | Best purpose | What to remember |
|---|---|---|
| Consolidate | Sum, Product, Average, Count, Max across ranges | Returns summaries rather than a full transaction table |
| Power Query | Bring together, clean and append source tables | Useful for repeatable refreshable data preparation |
| PivotTable | Filter, rearrange and analyse categories | Better for interactive summaries from structured source data |
| SUMIFS | Total values based on one or several conditions | Works well in a single structured transaction table |
| VSTACK | Stack ranges vertically in supported modern Excel | Retains original rows rather than summarising them |
A good example is invoice tracking. If you need the total invoice value per customer from a standardized set of monthly worksheets, consolidation may be enough. If you need invoice number, due date, payment status and every invoice record for a detailed dashboard, Power Query or another structured data model is a more appropriate starting point.
Excel Consolidate frequently asked questions
1. Can Excel Consolidate combine data from different workbooks?
Yes. Add source references from other open or browsed workbooks in the Consolidate dialog. Check the external links carefully when files are moved or renamed.
2. Does Consolidate merge every transaction row?
No. Consolidate calculates numerical summaries. Use Power Query or VSTACK when you need to append the original rows into one list.
3. Is the Product function really multiplication?
Yes. Product multiplies numeric values that correspond to a selected position or matching category. It is suitable for factors or other quantities intended to be multiplied, not for simply combining revenues.
4. Does Count count products sold?
Count counts numeric entries in the source ranges. To calculate the total quantity sold, use Sum on your quantity values instead.
5. What happens when two worksheets use different category labels?
They may appear as different categories in the output. Standardise product, department or category names before running Consolidate by category.
6. Can I use Average for branch sales?
Yes, if the question is the average of the included numeric branch amounts. It should not be confused with a weighted unit selling price, which may require quantities.
7. Can I automatically update the summary later?
The Create links to source data option can maintain linked results. If it is unchecked, the output is static. New ranges and major structural changes may still need manual attention.
8. Why does the Consolidate command not appear?
Availability differs by Excel platform. Microsoft's support documentation describes the feature for supported desktop versions of Excel; Excel for the web may not offer the same command.
9. Can I consolidate text values using this command?
Consolidate is designed for numerical calculations. Use TEXTJOIN for combining text cells or Power Query for appending mixed text-and-number records.
10. Which is better, Consolidate or PivotTable?
Consolidate is convenient for fixed numeric summaries from multiple ranges. PivotTables are more flexible when you want to filter, group and rearrange a structured dataset.
Watch TechBikash28 Excel Shorts
If you prefer seeing Excel demonstrated rather than reading every step, these are the three video Shorts already associated with the previous Consolidate page. Open any video to follow the related Excel learning content, then return to the example tables above to practise the Consolidate calculations.
These are the three supplied Shorts. Video availability and the exact subject of each clip depend on the YouTube uploads.
Explore the Excel Web Stories
Short visual lessons can be a good way to revisit a concept after you've worked through a longer tutorial. The first story focuses on Excel Consolidate; the others introduce numbers-to-words conversion, dashboards and XLOOKUP. They're included from the existing TechBikash28 story collection.
Final thoughts: choose the function that answers your question
Excel Consolidate is useful because it turns separate numerical reports into one readable summary. But the biggest improvement doesn't come from using the command quickly. It comes from choosing the right function, preparing consistent labels and checking the output against the source data.
We've used the same two-branch sales records to calculate a combined total, an average and a maximum, and we've used separate examples for multiplication and counting recorded values. That separation is deliberate. A total, a product, an average, a count and a maximum may all be mathematically correct, yet only one of them may answer the actual business question.
Start with the Sum example if you're new to Consolidate. Once you can repeat that confidently, try the other four functions and change a few source values to see how the output behaves. This is one of the easiest ways to move from memorising Excel steps to understanding the calculation.