MO-201 · Question #3
On the "Summary" worksheet, starting in cell A4, consolidate data from the 2014-2018 "By Region" worksheets. Display the average number of the "Recreation Visits" for each "Region". Use labels in both
Excel Data Consolidation - Explanation Overall Goal The task asks you to use Excel's Data > Consolidate feature to pull matching data from five separate worksheets (one per year, 2014–2018) and compute a single average for each Region - all landing neatly on the Summary sheet sta
Question
Explanation
Excel Data Consolidation - Explanation
Overall Goal
The task asks you to use Excel's Data > Consolidate feature to pull matching data from five separate worksheets (one per year, 2014–2018) and compute a single average for each Region - all landing neatly on the Summary sheet starting at A4.
Consolidate is the correct tool here because the source data lives on multiple sheets with matching structure (same Region labels, same column headers). It automates what would otherwise require writing complex cross-sheet formulas manually.
Step-by-Step with Reasoning
1. Click cell A4 on the Summary worksheet
This sets the output anchor - where Consolidate will place its top-left result. Skipping this or clicking the wrong cell means your consolidated table lands in the wrong place, potentially overwriting existing data.
2. Go to Data tab → Consolidate
This opens the Consolidate dialog. There is no other built-in Excel tool that aggregates across multiple sheets by matching labels this cleanly.
3. Set the Function to "Average"
The question specifically asks for the average number of Recreation Visits, not a sum or count. Leaving it on the default (Sum) produces wrong results without any error - Excel won't warn you.
4. Add each "By Region" worksheet range as a reference
For each of the five worksheets (2014 through 2018):
- Click in the Reference field
- Navigate to the sheet, select the data range (including the Region column and Recreation Visits column headers)
- Click Add
This tells Consolidate exactly where to find the source data. Missing even one year means that year's visits are excluded from the average, silently skewing results.
5. Check "Top row" AND "Left column" under Use labels in
- Top row: tells Excel to use the column headers (e.g., "Recreation Visits") to align data by column name across sheets
- Left column: tells Excel to use the Region labels (e.g., "Northeast", "Southeast") to align data by row name
If you omit either checkbox, Excel aligns by position instead of by label. This breaks the output if sheets have regions in different orders, or it drops the descriptive row/column labels entirely - leaving you with raw numbers and no context.
6. Click OK
Excel writes the averaged results into the Summary sheet starting at A4, with Region names in column A and the average Recreation Visits in the adjacent column.
What Goes Wrong if Steps Are Skipped
| Skipped Step | Result |
|---|---|
| Wrong starting cell | Table overwrites wrong area |
| Wrong function (Sum instead of Average) | Numbers are totals, not averages - no warning |
| Missing a year's reference | Average is computed from fewer data points |
| Only "Top row" checked (no Left column) | Regions not matched by name; output loses row labels |
| Only "Left column" checked (no Top row) | Column headers not matched; wrong columns aggregated |
Memory Tip
Think of the two label checkboxes as answering: "What does Excel use to line things up?"
- Top row = align by column name (what kind of data)
- Left column = align by row name (which region)
You need both because your data has identity in two dimensions. A quick mental check: "Does my source data have headers? Check top row. Does it have row labels? Check left column."
Topics
Community Discussion
No community discussion yet for this question.