MO-211 Expert Prep
Module 3 · Lesson 3.9

Summarize Data Using the Consolidate Feature

Edit on GitHub

In data engineering, combining data from multiple sources into one unified view is called aggregation or data consolidation. Excel's built-in Consolidate feature does exactly this for multiple ranges or worksheets — summing, averaging, or counting values across separate tables by matching position or label, without requiring formulas or Power Query.

Part 1: What Consolidate Does

The Consolidate feature combines data from: - Multiple ranges on the same worksheet - Ranges on different worksheets in the same workbook - Ranges in different workbooks

It aggregates the data using a function you choose (Sum, Average, Count, etc.) and outputs the result in a destination range. Unlike a formula, the consolidation is a snapshot — it does not update automatically unless you re-run it (unless you enable links).

Part 2: Opening the Consolidate Dialog

  1. Click the destination cell — the top-left cell where you want the consolidated output to begin.
  2. Go to Data tab > Data Tools group > Consolidate.

Part 3: Configuring the Consolidate Dialog

Function

Choose the aggregation method:

Function What It Does
Sum Adds all matching values (most common)
Average Averages matching values
Count Counts numeric entries
Max / Min Returns the highest or lowest value
Product Multiplies values together
Count Nums Counts only numeric cells (ignores text)
StdDev / Var Statistical measures

Reference — Adding Source Ranges

  1. Click in the Reference field.
  2. Navigate to your source range (on any sheet or workbook).
  3. Click Add. The range appears in the All references list.
  4. Repeat for each additional source range.
  5. Use Delete to remove a range from the list.
TIP
To reference another sheet, click its tab while the Reference field is active: the reference auto-populates as `Sheet2!$A$1:$C$10`. To reference another workbook, open it and click its range — the full path is inserted automatically.

Use Labels In

Controls how matching is performed:

Option Behavior
Top row Matches columns by their header labels
Left column Matches rows by their row labels
Both checked Matches by both row and column labels
Neither checked Consolidates by position — row 1 with row 1, column A with column A, regardless of labels
IMPORTANT
If your source ranges have **identical structures** (same columns in the same order), position-based consolidation (neither box checked) is fastest. If columns may appear in different orders across sources, use **Top row** label matching.

Create links to source data

When checked, Excel creates formulas (with INDIRECT-style external references) instead of plain values. This means the consolidated output updates automatically when the source data changes.

IMPORTANT
If you enable **Create links to source data**, Excel automatically groups the detail rows using the Outline feature, and you cannot edit the destination range directly. Uncheck this option if you want a static snapshot that you can edit freely.

Part 4: Step-by-Step Example

Scenario: You have quarterly sales data on four sheets (Q1, Q2, Q3, Q4), each in the same A1:C10 layout. You want a full-year summary on a "Summary" sheet.

  1. Click cell A1 on the Summary sheet.
  2. Open Data > Consolidate.
  3. Set Function to Sum.
  4. Click in Reference, go to the Q1 sheet, select A1:C10, click Add.
  5. Repeat for Q2, Q3, Q4.
  6. Check Top row and Left column (to match by headers and row labels).
  7. Click OK.

The Summary sheet now contains the sum of all four quarters, matched by label.

Part 5: Consolidate vs. Other Approaches

Approach Dynamic? Requires same layout? Best for
Consolidate No (unless links enabled) No (label matching available) Quick cross-sheet summary
3D formulas (=SUM(Q1:Q4!B2)) Yes Yes (identical layout required) Fixed-structure multi-sheet totals
Power Query Yes (on refresh) No Complex multi-source ETL
PivotTable Yes (on refresh) Yes (single flat table) Interactive summary analysis

Official Resources