Theory
Twelve sheets, one yearly total
Meera keeps one sheet per month: Jan, Feb, Mar, each listing her items and their sales. At year end she wants one summary: total yearly sales per item across all twelve sheets.
She could type a giant formula adding cells from twelve sheets by hand, error-prone and painful. Or she could use the one tool built exactly for this: Consolidate, which merges many sheets into a single summary and does the arithmetic for her.
This is the last piece of Unit 2, and a genuine time-saver.
Theory
Merging twelve attendance registers
Twelve monthly attendance registers, one per month, and you want each student's yearly total. You would flip through all twelve, find 'Riya' in each, and add up her days. Consolidate is an assistant who does exactly that: it finds each matching label across every sheet and applies your chosen function (add them, average them, find the max) into one clean summary register.
Theory
How Consolidate works
Data tab > Consolidate. You give it:
- a function (Sum, Count, Max, Min, Average),
- the ranges to combine (Jan sheet, Feb sheet, Mar sheet...),
- how to match: by position (every sheet has the identical layout, so cell-for-cell) or by category (match on the row/column labels, even if items are in a different order per sheet).
Tick Create links to source data and the summary stays live, editing a monthly sheet updates the yearly total automatically.
Quiz
Meera's three monthly sheets list items in DIFFERENT orders (Jan starts with Sugar, Feb with Rice). Which consolidation method should she use?
- By category, it matches on the item labels regardless of order
- By position, it matches cell-for-cell
- Neither, consolidation needs identical sheets
- She must manually reorder all sheets first
Show the answer
By category, it matches on the item labels regardless of order
By category matches on the row/column labels (item names), so it correctly pairs 'Sugar' with 'Sugar' even when the sheets are ordered differently. By position only works when every sheet has the identical layout (cell-for-cell). Since Meera's orders differ, category matching is the safe choice, a standard exam discrimination.
Think first
Sum or Average?
Meera consolidates her three months. If she picks the SUM function she gets one number per item; if she picks AVERAGE she gets another. For 'total yearly sales per item', which function, and what would AVERAGE have told her instead?
Show the answer
SUM, it adds each item's sales across all three months into a yearly total. AVERAGE would instead give the typical monthly sales per item (the mean across the three months), a different and also useful figure. The tool is the same; the function you choose decides the meaning of the summary. Pick the function that answers your actual question.
Watch out
Where marks leak
Confusing by position (identical layouts, cell-for-cell) with by category (matches labels, handles different orders), the key discrimination. Forgetting that the chosen function defines the result (Sum vs Average give different meanings). And missing the Create links option that keeps the summary live. Also: Consolidate is a summarising tool across ranges, not the same as a single SUM within one sheet, exams may contrast them.
Theory
Unit 2 done, and a bridge to databases
Consolidation, grouping data by a label and summarising each group, is exactly what SQL's GROUP BY does in a database, and what a PivotTable does interactively. You have now met the idea in a spreadsheet before meeting its name. Unit 2 is complete: Meera can compute, chart, clean and summarise. Unit 3 asks the big question this whole subject has been building toward: when should she stop using Excel and use a database instead?
Summary
Key takeaways
- Consolidate (Data tab) merges multiple sheets/ranges into one summary using a chosen function.
- Function options: Sum, Count, Max, Min, Average, and the function decides the summary's meaning.
- Match by POSITION (identical layouts, cell-for-cell) or by CATEGORY (matching labels, any order).
- Create links to source data keeps the summary live as sources change.
- It is the spreadsheet ancestor of SQL GROUP BY and PivotTables.
- Memory hook: an assistant merging twelve registers by matching each label.