Microsoft Excel · Data analysis and summaries · Intermediate
Consolidate worksheet summaries by position or category
Combine two department summaries safely, checking matching labels rather than trusting a correct grand total.
First-party documentation reviewed; original arithmetic independently checked; not executed in Excel · Editorial standards
Quick answer: Use Data > Consolidate to aggregate separate summaries. Match categories when labels move; use position only when every corresponding cell has the same meaning. A correct overall total does not prove that individual categories are correct.
Scope and preparation
This is a documentation-based Windows desktop workflow for an already licensed Excel for Microsoft 365, Excel 2024 or Excel 2021 installation. It is not a free-web lesson, a purchase recommendation or a native application test. The synthetic data and expected answers below were checked independently of Excel.
Microsoft distinguishes position and category consolidation. Keep source lists free of empty rows and columns, and reserve a blank destination with room below and to the right. Leave Create links to source data unchecked for this exercise: the output is a snapshot, so changed inputs require reconsolidation.
Create sheets named North, South, AlignedSouth, ByCategory and ByPosition in a disposable workbook. The amounts represent expense units, not transaction records. Paste this original fixture into North!A1:C4:
Expense Jan Feb
Travel 120 80
Supplies 40 60
Training 90 110
Paste this deliberately rearranged fixture into South!A1:C4:
Expense Feb Jan
Training 30 70
Travel 50 150
Supplies 20 30
For the position comparison, enter the same South observations in North’s order at AlignedSouth!A1:C4:
Expense Jan Feb
Travel 150 50
Supplies 30 20
Training 70 30
North totals 500; either South representation totals 350. AlignedSouth is an alternative input, not a third department. Never add South and AlignedSouth to the same consolidation.
Match the labels, not their locations
The official category instructions document the label controls used here.
- Select ByCategory!A1. Choose Data > Consolidate, then Sum in Function.
- Under Use labels in, select Top row and Left column.
- Use Reference to select North!$A$1:$C$4; choose Add. Add South!$A$1:$C$4 in the same way. Include the labels in both selections.
- Confirm that All references contains only those two ranges, and that source links remain unchecked. Choose OK.
Compare the result by the displayed row and column labels, not an assumed output ordering. These are the expected category intersections:
| Expense | Jan | Feb | Independent row check |
|---|---|---|---|
| Travel | 270 | 130 | 400 |
| Supplies | 70 | 80 | 150 |
| Training | 160 | 140 | 300 |
The last column is your audit calculation, not an extra field promised by Consolidate. Jan totals 500, Feb totals 350, and all six output amounts total 850. For example, Travel/Jan is 120 + 150, not 120 + 30. Do not depend on the top-left corner displaying the word Expense.
Compare a genuinely position-aligned run
Select ByPosition!B2, leaving B2:C4 empty. Repeat the Consolidate operation with North!$B$2:$C$4 and AlignedSouth!$B$2:$C$4, Sum, both label boxes cleared, and source links unchecked. Remove any references left over from the category run before adding these two numeric-only ranges. The documented position semantics distinguish positional aggregation from title-based matching; no VBA is required here.
B2:C4 should contain 270/130, 70/80 and 160/140. After checking the numbers, add Jan/Feb at B1:C1 and Travel/Supplies/Training at A2:A4. These labels are supplied by you because this run selected only numbers.
Why the tempting shortcut is wrong
As an independent arithmetic counterexample, pair North!B2:C4 with the numeric block from the reordered South sheet by position. The resulting pairs would be 150/150, 90/210, 110/140. Those are not the three categories above. Yet their grand total is still 850. Checking only the grand total would miss both the row and month mismatch.
Changed-input check and troubleshooting
In the practice workbook, change South!C3 from 150 to 155. Reconsolidate the category run with its original two labeled ranges. Travel/Jan should become 275, Jan 505, Travel’s row total 405, and the overall total 855; every other intersection stays unchanged. AlignedSouth is a separate fixture and does not update itself. Restore the 150 before comparing the two initial runs again.
- Extra categories: inspect spelling and label consistency; labels that differ are not automatically equivalent.
- Wrong totals: check for the wrong function, duplicate source references, included subtotal rows or the accidental addition of AlignedSouth.
- Stale result: this exercise intentionally makes a snapshot. Recheck the references before rerunning; do not silently switch to linked output.
- Missing command: confirm the desktop edition before continuing. This guide does not establish browser availability.
Save the practice workbook separately and check the six labeled intersections after reopening. Consolidation summarizes amounts; it does not enrich transaction rows with lookup attributes. For that different task, use merging related tables with Power Query.