The Twelve-Sheet Nightmare
If you are a regional manager, you likely receive twelve identical spreadsheets at the end of the year—one for each month. Each sheet has the exact same columns: “Product Name,” “Units Sold,” and “Revenue.” Your boss now wants a single “Yearly Summary” sheet that adds all twelve months together.
The manual approach is soul-crushing. You create a thirteenth blank sheet, copy the Product Names from January’s sheet, and then start writing SUM formulas that reach across all twelve other sheets. For a sheet with fifty products, you are writing fifty formulas, each referencing twelve different worksheets. If you make a single typo in the sheet name reference, the formula breaks silently, and your entire report is wrong.
Excel has a built-in wizard designed specifically to solve this problem in under thirty seconds. It is called Data Consolidation. You point it at multiple identically structured tables, tell it to “Sum,” and it instantly merges them into a single master summary.
Setting Up the Data
For Consolidation to work cleanly, your source data must follow one rule: all the source sheets must have the same column structure.
For example:
- Sheet “January”: Row 1 headers = Product | Units | Revenue. Rows 2-51 contain the data.
- Sheet “February”: Row 1 headers = Product | Units | Revenue. Rows 2-51 contain the data.
- …and so on for all twelve months.
Running the Consolidation Wizard
- Create a new, blank worksheet in your workbook. Name it “Yearly Summary.”
- Click on cell A1 of this new blank sheet. This is where the merged data will be placed.
- Go to the Data tab on the Excel ribbon.
- Click the Consolidate button (it is usually in the “Data Tools” group).
A small Consolidate dialog box will appear.
Step 1: Choose the Function
The “Function” dropdown at the top defaults to Sum. This is exactly what you want for adding revenue across months. (Other options include Count, Average, Max, and Min, which are useful for different types of analysis).
Step 2: Add the References
Now you must tell Excel where the twelve source tables are located.
- Click inside the Reference box in the dialog.
- Using your mouse, click over to the “January” sheet tab and highlight the entire data range including headers (e.g.,
January!$A$1:$C$51). - Click the Add button. The reference will appear in the “All references” list below.
- Repeat: click the “February” sheet, highlight the same range, and click Add.
- Continue for all twelve monthly sheets.
Step 3: Match by Labels
This is the most critical step. At the bottom of the dialog, you will see two checkboxes:
- Top row: Check this box. It tells Excel to match the columns by their header names (“Product,” “Units,” “Revenue”), not by their physical position.
- Left column: Check this box. It tells Excel to match the rows by the product names in the first column, not by their row number.
This is vital because if “Widget A” is on row 5 in January but row 12 in March, Excel will still correctly add their revenue together because it is matching by the label “Widget A,” not by the row number.
Step 4: Click OK
Click OK. In less than one second, your blank “Yearly Summary” sheet will populate with a perfectly aggregated master table. Every product’s units and revenue from all twelve months will be summed together into a single, clean row.
Conclusion
Never manually write cross-sheet SUM formulas when you need to merge identically structured tables. By using the Excel Data Consolidation wizard, you can merge twelve months of data into a yearly summary in under thirty seconds, completely eliminating formula errors and saving hours of tedious work.