Analistable

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

Ways to consolidate data in Excel
MethodConsolidatesUpdatesNotes
Data → ConsolidateSame labels across sheetsOptional linksBuilt in to every version
3D SUMSame cell across sheetsLiveSheets must share a layout
SUMIFS per sheetCriteria across sheetsLiveFlexible, longer formulas
Pivot tableDuplicate rows in one tableOn RefreshBest for exploring
Power Query Group ByRows from any number of sourcesOn RefreshBest for repeat jobs

Method 1: the Consolidate tool

  1. Select an empty cell on a summary sheet.
  2. Choose Data → Consolidate.
  3. Pick a function: Sum, Count, Average, Max, Min and others.
  4. Click into Reference, select the range on the first sheet including labels, and click Add. Repeat for each sheet.
  5. Tick Top row and Left column so rows and columns are matched by label, not position.
  6. 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.

Consolidated with Sum, matched by label
ProductJanFebMarConsolidated
Desk120090011003200
Chair640710—1350
Lamp—300260560

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

  1. Open every source workbook as well as the one that will hold the summary.
  2. In the summary workbook, start Data → Consolidate and click into Reference.
  3. 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.
  4. 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.

Budget by department and consolidated total
LineMarketingSalesOpsSummary =SUM(Start:End!B…)
Salaries42,00058,00037,500137,500
Travel3,5009,8001,20014,500
Software6,2002,4004,90013,500
Total51,70070,20043,600165,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.

Invoice lines
CustomerAmount
Atlas120
Bloom80
Atlas45
Cedar200
Bloom60
Atlas35
Cedar50
Delta90
  1. Click in the data and press Ctrl+T to format it as a table, so the pivot picks up rows added later.
  2. Choose Insert → PivotTable and place it on a new sheet.
  3. Drag Customer to Rows and Amount to Values (Sum of Amount).
  4. Drag Amount to Values a second time, then use Value Field Settings to change it to Count.
Pivot table result
CustomerSum of AmountCount of Amount
Atlas2003
Bloom1402
Cedar2502
Delta901
Grand Total6808

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.

  1. Choose Data → Get Data → From File → From Folder and pick the folder holding the monthly files.
  2. 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.
  3. Select the column to consolidate by, such as Product, and choose Home → Group By.
  4. 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.
  5. 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

Consolidation problems and fixes
SymptomLikely causeFix
The same label appears twice in the resultLabels differ by a trailing space or spellingClean labels with TRIM or Find & Replace, then re-run
Result has numbers but no labelsTop row and Left column not tickedTick both and consolidate again
Totals land against the wrong productsConsolidated by position on sheets with different layoutsConsolidate by category instead
3D SUM total is too lowA sheet sits outside the Start:End rangeDrag it between the bookend sheets
Summary doesn't change when sources doConsolidate ran without linksRe-run, or tick Create links to source data
Pivot table misses new rowsThe source is a fixed rangeFormat the source as a table, then Refresh
Power Query totals are doubledA file is in the folder twice, or the output workbook is inside itCheck Source.Name and move the output file out
#REF! in an INDIRECT formulaA sheet name in the list is misspelled or was renamedCorrect 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

  1. Save each month's file or sheet with the same layout and a predictable name.
  2. Refresh or re-run the consolidation: Refresh All for pivots and Power Query, OK in the Consolidate dialog, nothing for 3D formulas.
  3. Compare the control total with the source total and investigate any difference before sending the report.
  4. 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

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.