Using Consolidate you can summarize data from separate worksheets onto one master sheet. Data Consolidation performs a statistical function (e.g. Sum, Average) on a series of ranges (lists). And the best part: the lists need not have identical content.
Watch this 2+ min. video to learn all you need to know.
Performing the Data Consolidation
Click here to open Toy List Consolidation.xlsx sample spreadsheet
Prep: Sort each list by the first column and remove all blank rows and columns within the lists.
Tip: Use Define Name to set name each list.
- On the summary sheet, select the upper-left cell where the consolidation is to appear.
- On the Data tab, in the Data Tools group, click Consolidate.
- In the Function box, select function (i.e., Sum).
- In the Reference field, type the name or cell references of first list (including column and row headings), then click Add. Repeat this step for each list to be summarized.
- Check Use labels in Top row and Use labels in Left column.
- Click OK.
Comments and questions are always welcome!