Level 5 · Consolidate, PivotTable or master table?

You have now seen three ways to bring several sources together. They overlap, and the menu names don’t help: Excel’s tool called Consolidate is often not the best way to consolidate. Choosing well comes down to what the sources contain and what will be asked of the result.

The three approaches side by side

	Consolidate (position or label)	Stack + PivotTable	Formula master table
Sources are	summary blocks (totals per category, per month)	detail rows (order lines)	anything with a key
Output	one grid of totals	a pivot you can rearrange	the table you design
Row order and new items	label mode handles both	handled	you maintain the key list
Updates when sources change	only with “Create links to source data”	Refresh (Power Query + pivot)	automatically, formulas
Best for	a one-off total of same-shaped reports	recurring reports, changing questions	small lists you want full control over

Consolidate: totals of totals

Levels 1 and 2 were Consolidate’s home ground: three category-by-month reports, or two monthly unit reports, added into one. The sources were already summaries, the question was fixed (“the total”), and it was needed once. The tool (Data → Consolidate) or the matching SUM / SUMIF formulas do it in minutes. Ask a second question, say revenue by day, and you have nothing to work from.

Stack and pivot: detail you can re-ask

Levels 3 and 4 kept every row. The cost is one step up front: the same columns everywhere and a source column. The reward is that any summary is one SUMIFS grid or one PivotTable away, and a new question doesn’t need new data. In Excel the stacking is Power Query’s Append (chapter 11), and the Data Model joins in when you also need a second table, such as products for categories.

The formula master table: control

The third option is a sheet you lay out yourself: one row per key (a product, a store), with XLOOKUP or INDEX/MATCH pulling each source’s value into its own column. You decide exactly what appears and you can clean values as you pull them. It is the right choice for a short, stable list, like comparing a catalog with last quarter’s price list, which is the boss level. It is the wrong one when keys appear and disappear every week, because a key missing from the list is silently missing from the totals.

Common mistakes

• Choosing by menu name. “Consolidate” sounds right for every combining job. It only fits summary blocks and one-off totals.
• Stacking without a source column. The combined table can no longer say which export a row came from.
• A master table with a stale key list. New products added to the sources never reach the report. Check the totals against the sources.