Consolidating data

Data rarely arrives in one piece. The web shop, the app and the marketplace each send their own export. Finance keeps one sheet per month. The buyer has last quarter’s price list and today’s catalog. Sooner or later someone asks for one number, or one table, built from all of them.

This chapter covers the three ways Excel offers to do that, and when each one fits. Consolidate by position adds blocks that share the same layout, cell by cell. Consolidate by label matches rows by their product id or category first, so the order of the rows stops mattering. A master table stacks every source into one list with a column saying where each row came from, and then SUMIFS or a PivotTable summarises it any way you like. The boss level compares two versions of the same list: what is new, what is gone, and what changed.

In Excel the sources usually sit on separate sheets, one per channel or month. The practice sheet here is a single worksheet, so the blocks sit one under another instead. The formulas are the same ones you would write across sheets, and each lesson shows the multi-sheet version too.