How to consolidate data in Excel
By the Analistable team · Updated · 12 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.
Consolidate by position or by category
The two label tick boxes decide how the Consolidate tool lines up the source ranges, and choosing wrongly is the most common reason for wrong totals.
- By position (neither box ticked): Excel adds the first cell of every range together, then the second, and so on. It only works when every sheet has exactly the same rows and columns in the same order, like copies of one template. The result has no labels, so you type them yourself.
- By category (Top row and/or Left column ticked): Excel reads the labels and adds values with the same label, wherever they sit. Products can be in any order, and a product that appears on only one sheet still gets its own row.
- Mixed: tick only Left column when every sheet has the same column headings in the same order but the row items differ — for example the same Jan–Mar columns with a different list of customers on each regional sheet.
Matching by category compares the label text, so "Desk" and "Desk " with a trailing space become two rows. Clean labels with TRIM or Find & Replace before consolidating, and scan the result for near-duplicates.
What Create links to source data does
Without links, Consolidate writes static numbers: run it again whenever a source changes. With Create links to source data ticked, Excel builds an outline instead. Each consolidated row becomes a summary row with hidden detail rows above it, one per source, each holding a formula that points to the source cell. The 1 and 2 buttons to the left of the row numbers collapse and expand that detail.
Links only work when the sources are on other sheets or in other workbooks, not on the sheet that holds the result. Adding a new source later still means running Consolidate again, because the outline is built once. If you need a summary that picks up new sheets and new labels without re-running anything, Power Query or a pivot over one stacked table is the better route.
Consolidating ranges from other workbooks
- Open every source workbook as well as the one that will hold the summary.
- In the summary workbook, start Data → Consolidate and click into Reference.
- Switch to a source workbook (View → Switch Windows, or click its window), select the range including labels, and click Add. The reference appears with the file name in square brackets, such as
'[North.xlsx]Jan'!$A$1:$C$20. - Repeat for each workbook, tick the label boxes, and click OK.
Excel remembers the list of references for that summary sheet, so next month you can open the dialog, check the list and click OK again. If the source files are renamed or moved, linked results show old values or errors; update the paths in Data → Edit Links (called Workbook Links in recent Microsoft 365 versions). Keeping the source files in one folder with stable names avoids most of this.
Worked example: department budgets with a 3D SUM
Three department sheets — Marketing, Sales and Ops — are copies of one template, with Salaries, Travel and Software in B5:B7. Add two empty sheets called Start and End and drag the department sheets between them. On the Summary sheet, =SUM(Start:End!B5) totals B5 on every sheet in between; fill it down to B7.
| Line | Marketing | Sales | Ops | Summary =SUM(Start:End!B…) |
|---|---|---|---|---|
| Salaries | 42,000 | 58,000 | 37,500 | 137,500 |
| Travel | 3,500 | 9,800 | 1,200 | 14,500 |
| Software | 6,200 | 2,400 | 4,900 | 13,500 |
| Total | 51,700 | 70,200 | 43,600 | 165,500 |
The column totals add up the same way across: 51,700 + 70,200 + 43,600 = 165,500. To add a fourth department, copy the template and drag the copy between Start and End — the formulas include it at once. The two risks are a sheet dragged outside the bookends, which drops out of the total without warning, and a row inserted on one sheet only, which shifts its Salaries line so that B5 no longer means the same thing everywhere. 3D references work the same way in Excel for Windows and Mac; Google Sheets doesn't support them.
SUMIFS across sheets in any Excel version
SUMIFS can't take a 3D reference, and FILTER with VSTACK needs Microsoft 365. In older versions, list the sheet names in a range, name it Sheets, and let INDIRECT build one reference per sheet:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C2:C500"), INDIRECT("'"&Sheets&"'!A2:A500"), "Desk"))SUMIFS runs once per sheet and SUMPRODUCT adds the results. The single quotes around each name let sheet names contain spaces. INDIRECT has two drawbacks: it recalculates on every change in the workbook, which slows large files, and it doesn't follow renamed sheets, so the name list must be updated by hand.
Worked example: one row per customer with a pivot table
An invoice list repeats customers. The goal is one row per customer with the total and the number of invoices.
| Customer | Amount |
|---|---|
| Atlas | 120 |
| Bloom | 80 |
| Atlas | 45 |
| Cedar | 200 |
| Bloom | 60 |
| Atlas | 35 |
| Cedar | 50 |
| Delta | 90 |
- Click in the data and press Ctrl+T to format it as a table, so the pivot picks up rows added later.
- Choose Insert → PivotTable and place it on a new sheet.
- Drag Customer to Rows and Amount to Values (Sum of Amount).
- Drag Amount to Values a second time, then use Value Field Settings to change it to Count.
| Customer | Sum of Amount | Count of Amount |
|---|---|---|
| Atlas | 200 | 3 |
| Bloom | 140 | 2 |
| Cedar | 250 | 2 |
| Delta | 90 | 1 |
| Grand Total | 680 | 8 |
The grand total of 680 matches the sum of the eight invoice lines. In current Microsoft 365 versions, =GROUPBY(A2:A9, B2:B9, SUM) returns the same summary as a formula that updates without a refresh. When the rows sit on several sheets, see building a pivot table from multiple sheets.
Power Query Group By step by step
Power Query suits a consolidation you repeat every month from files that arrive in a folder.
- Choose Data → Get Data → From File → From Folder and pick the folder holding the monthly files.
- Click Combine & Transform Data, choose the sheet or table to use from the sample file, and click OK. Power Query appends every file and adds a Source.Name column with each file name.
- Select the column to consolidate by, such as Product, and choose Home → Group By.
- Switch to Advanced, name the first new column Total with operation Sum on Amount, then click Add aggregation for a Rows column with operation Count Rows.
- Click Close & Load. Next month, save the new file into the folder and click Data → Refresh All.
= Table.Group(Combined, {"Product"}, {
{"Total", each List.Sum([Amount]), type number},
{"Rows", each Table.RowCount(_), Int64.Type}
})Keep the workbook that holds the query outside the source folder, otherwise it can be read as one of the files to combine. Power Query in Excel for Mac has gained features later than Windows, so check which sources your version offers under Data → Get Data. More detail: combining files from a folder, and a side-by-side view in Power Query vs Analistable.
Consolidating in Google Sheets
Google Sheets has no Consolidate command and no 3D references. The equivalent is a QUERY with group by over a stack of tabs, such as =QUERY(VSTACK(Jan!A2:B, Feb!A2:B), "select Col1, sum(Col2) where Col1 is not null group by Col1", 0). The full set of patterns is in combining data in Google Sheets.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| The same label appears twice in the result | Labels differ by a trailing space or spelling | Clean labels with TRIM or Find & Replace, then re-run |
| Result has numbers but no labels | Top row and Left column not ticked | Tick both and consolidate again |
| Totals land against the wrong products | Consolidated by position on sheets with different layouts | Consolidate by category instead |
| 3D SUM total is too low | A sheet sits outside the Start:End range | Drag it between the bookend sheets |
| Summary doesn't change when sources do | Consolidate ran without links | Re-run, or tick Create links to source data |
| Pivot table misses new rows | The source is a fixed range | Format the source as a table, then Refresh |
| Power Query totals are doubled | A file is in the folder twice, or the output workbook is inside it | Check Source.Name and move the output file out |
| #REF! in an INDIRECT formula | A sheet name in the list is misspelled or was renamed | Correct the name list |
Consolidating more than totals
Sum is the default, but the other functions in the Consolidate dialog (and in pivot tables and Group By) answer different questions:
- Max on a date column gives the latest date per label, for example each customer's most recent order across regional sheets, because Excel stores dates as numbers.
- Count counts non-empty cells per label, while Count Numbers counts only numeric ones — useful when some sources leave text such as "n/a" in a number column.
- Average needs care. If each monthly sheet already holds an average, consolidating them with Average gives an average of averages, which is wrong when months have different volumes. Consolidate the sums and the counts instead, then divide one by the other.
Common mistakes
- Selecting ranges without the label row or column, so Excel has nothing to match by and falls back to position.
- Placing the result on a sheet that is inside a 3D range, which makes the formula count its own output.
- Consolidating a sheet that already contains a subtotal row, so the subtotal is added again as if it were a product.
- Copying a consolidated result with links into another workbook, where the outline formulas still point at the original sources.
- Assuming a pivot table updates itself: it changes only when you click Refresh, or when the workbook is set to refresh on opening.
A monthly consolidation routine
- Save each month's file or sheet with the same layout and a predictable name.
- Refresh or re-run the consolidation: Refresh All for pivots and Power Query, OK in the Consolidate dialog, nothing for 3D formulas.
- Compare the control total with the source total and investigate any difference before sending the report.
- Scan the label column for new or misspelled items — a new product name is often the first sign that a source changed its layout.
Every guide in this topic
- Cash flow consolidation template
Free Excel template that rolls up cash flows from several entities into one group view, with opening and closing cash and an intercompany check.
- Consolidation template for Excel
General-purpose Excel consolidation template: stack data from any number of sources in one sheet and get a category-by-source grid with totals and checks.
- Excel Consolidate not working: why it doesn't update or ignores rows
Excel Consolidate not updating or ignoring rows? Fix static results, labels that don't match, ranges that miss new data and text stored as numbers.
- Expense consolidation template
Free Excel template that stacks expense claim lines from every employee, checks each line against category limits and totals spend by category.
- How to append monthly files without duplicates
Monthly exports often overlap. Append them each month and remove repeats on a natural key in Power Query, Excel formulas or Python, keeping the latest version.
- How to build a consolidation worksheet template in Excel
Download a free consolidation worksheet template for Excel, or build your own: identical input sheets, 3D SUM formulas and check totals that catch errors.
- How to combine branch inventory counts into one spreadsheet
Combine stock count sheets from every branch into one company-wide view by SKU, with branch totals, gaps and over-stock visible side by side.
- How to combine class and enrolment lists into one master roster
Combine class lists and course enrolment exports into one master roster, with one row per student and no duplicates from students in several classes.
- How to combine franchise P&L and department budget files into one rollup
Roll up franchise P&Ls and department budgets that use different account lines by mapping each line to a shared chart of accounts first.
- How to combine monthly bank statements into one spreadsheet
Stack monthly bank and fuel card statement exports into one table with consistent dates and signs, and remove transactions duplicated across overlaps.
- How to combine monthly expense claims into one spreadsheet
Turn a folder of per-employee expense claim sheets into one ledger you can approve, total by category and check against policy limits.
- How to combine monthly, store and country sales reports into one spreadsheet
Stack monthly, per-store and per-country sales files into one table with period, store and currency columns so you can total and compare them.
- How to combine project status trackers into one spreadsheet
Roll up each team's project tracker into one portfolio view with consistent statuses, owners and dates, without asking teams to change tools.
- How to combine property rent rolls into one portfolio rent roll
Build a portfolio rent roll from per-property spreadsheets, then report occupancy, passing rent and arrears for every building in one table.
- How to combine weekly timesheets into one spreadsheet
Stack weekly timesheet files from every person into one long table so payroll hours and project hours both come from the same source.
- 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.
- Inventory consolidation template
Free workbook that combines stock counts from several locations into one table: quantity per site, total stock value and a reorder flag per SKU.
- Monthly reporting consolidation template
Free Excel template that combines monthly figures from each unit into one report of revenue, costs, profit, margin and headcount, with a submission check.
- Multi-entity P&L consolidation template
Free Excel template that converts each entity's P&L to one currency, applies intercompany eliminations and builds a consolidated P&L by line.
- Multi-store sales template
Free multi-store sales workbook that rolls up sales lines from every shop into units, revenue, % of target and a ranking per store and region.
- Sales by region roll-up template
Free workbook that stacks regional sales exports, totals each region with SUMIFS and compares it with target, variance and share of total sales.
- Student grades roll-up template
Free Excel and Google Sheets template that rolls marks from every assessment into one total and percentage per student, and flags missing work.
- Timesheet consolidation template
Free Excel template that stacks timesheet entries from every employee, compares hours with contracted hours and totals time by project.
- Why totals double after consolidating, and how to audit them
Consolidated total twice what it should be? Check for subtotal rows, a summary sheet inside the 3D range, overlapping ranges and one-to-many joins.
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.
- How do I combine duplicate rows and add up their values?
- Use a pivot table with the label in Rows and the number in Values, GROUPBY in current Microsoft 365, or Power Query Group By if the list is refreshed regularly.
- Is consolidating the same as merging?
- No. Consolidating summarises values into totals per label. Merging or appending keeps every row, either stacked or matched side by side on a key.
- Can I consolidate data in Excel for Mac?
- Yes. Data → Consolidate, 3D formulas such as =SUM(Jan:Dec!B2) and pivot tables all work in Excel for Mac. Power Query is also available in Microsoft 365 for Mac, though some data sources reached it later than on Windows.