How to consolidate data in Excel
By the Analistable team · Updated · 3 min read
To consolidate data in Excel, use Data → Consolidate: choose a function such as Sum, add the range from each sheet, tick Top row and Left column, and click OK. Excel totals matching labels across all sheets. For live formulas, use a 3D reference like =SUM(Jan:Dec!B2). To total duplicate rows within one list, use a pivot table or Power Query Group By.
What consolidation means
Consolidating summarises data from several places into one result: total sales per product across twelve monthly sheets, or one row per customer from a list where customers repeat. It's different from stacking, which keeps every row. If you need every row in one table, see combining sheets into one.
Methods compared
| Method | Consolidates | Updates | Notes |
|---|---|---|---|
| Data → Consolidate | Same labels across sheets | Optional links | Built in to every version |
| 3D SUM | Same cell across sheets | Live | Sheets must share a layout |
| SUMIFS per sheet | Criteria across sheets | Live | Flexible, longer formulas |
| Pivot table | Duplicate rows in one table | On Refresh | Best for exploring |
| Power Query Group By | Rows from any number of sources | On Refresh | Best for repeat jobs |
Method 1: the Consolidate tool
- Select an empty cell on a summary sheet.
- Choose Data → Consolidate.
- Pick a function: Sum, Count, Average, Max, Min and others.
- Click into Reference, select the range on the first sheet including labels, and click Add. Repeat for each sheet.
- Tick Top row and Left column so rows and columns are matched by label, not position.
- Optionally tick Create links to source data to keep the summary connected to the sources, then click OK.
Example: three sheets list sales by product in different orders and with different products. Consolidate by label returns one row per product with the total.
| Product | Jan | Feb | Mar | Consolidated |
|---|---|---|---|---|
| Desk | 1200 | 900 | 1100 | 3200 |
| Chair | 640 | 710 | — | 1350 |
| Lamp | — | 300 | 260 | 560 |
More detail and limitations: the Consolidate function in Excel.
Method 2: formulas across sheets
If every sheet has the same layout, =SUM(Jan:Dec!B2) adds cell B2 on every sheet between Jan and Dec. For criteria, SUMIFS doesn't accept 3D references, but in Microsoft 365 you can combine FILTER and VSTACK: =SUM(FILTER(VSTACK(Jan:Dec!C2:C500), VSTACK(Jan:Dec!A2:A500)="Desk")). See SUMIFS across multiple sheets.
Method 3: consolidate duplicate rows
When one list repeats the same customer or product, a pivot table gives one row per value with totals: select the data, Insert → PivotTable, drag the label to Rows and the number to Values. To merge the text in duplicate rows instead of adding numbers, use TEXTJOIN. See combining rows in Excel.
Method 4: Power Query Group By
Load the data (or several appended tables) into Power Query, select the label column and choose Home → Group By. Add an aggregation for each number column. The result refreshes when sources change, which suits monthly reporting.
Which method should you choose?
- Sheets with identical layouts (a budget template per department) → 3D SUM formulas or Consolidate by position.
- Sheets with the same labels in different orders → Consolidate with Top row and Left column ticked.
- One long list with repeated customers or products → pivot table, or Power Query Group By if it must refresh.
- Files that arrive every month → stack them with Power Query From Folder, then Group By or pivot.
Check the consolidated totals
A consolidation that silently drops a sheet or a row looks exactly like a correct one. Add a control total next to the result:
Total of sources: =SUM(Jan:Dec!C2:C500)
Total of consolidation: =SUM(Summary!C2:C200)
Difference: =ROUND(C1-C2, 2)Any difference other than 0 means a label didn't match (often a trailing space), a range missed new rows, or a sheet sits outside the 3D range. For a reusable layout with checks built in, see building a consolidation template.
Every guide in this topic
- How to build a consolidation worksheet template in Excel
Design a reusable Excel consolidation template: identical input sheets, a Start/End sandwich, 3D formulas for the summary and check totals that catch errors.
- How to merge rows in Excel without losing data
Merge several rows into one in Excel without losing data: TEXTJOIN into one cell, Fill → Justify for text blocks, and consolidating rows that share a key.
- How to use SUMIFS across multiple sheets
Sum with criteria across many Excel sheets: 3D SUM for same-layout sheets, SUMPRODUCT with INDIRECT and a sheet list, or FILTER and VSTACK in Microsoft 365.
- How to use the Consolidate function in Excel
Use Excel's Data → Consolidate to combine and summarise ranges from several sheets or workbooks: by position or by label, with links, and its limits.
Frequently asked questions
- How do I consolidate data from multiple sheets in Excel?
- Use Data → Consolidate, add each sheet's range, and tick Top row and Left column to match by labels. For a live result, use 3D formulas such as =SUM(Jan:Dec!B2).
- Why does Consolidate put numbers in the wrong rows?
- If Top row and Left column aren't ticked, Excel consolidates by position. Tick both so it matches by label.
- Can Consolidate combine text?
- No. Consolidate only aggregates numbers. Use TEXTJOIN, or Power Query Group By with a text aggregation, to merge text.
- Does Consolidate update automatically?
- Only if you tick Create links to source data, and then only for sources on other sheets or workbooks. Otherwise, run it again after the data changes.