Excel Consolidate not working: why it doesn't update or ignores rows
By the Analistable team · Updated · 5 min read
Consolidate produces static values unless you tick Create links to source data, so it won't update when the sources change: rerun it via Data → Consolidate → OK, or switch to linked mode or formulas. When rows are missing, the labels usually differ (extra spaces, spelling), the source references stop before the new rows, Top row or Left column isn't ticked, or numbers are stored as text and ignored by SUM.
Part of our guide: How to consolidate data in Excel
Why Consolidate doesn't update
By default, Data → Consolidate calculates once and pastes values. Change a source figure and the summary stays the same. That is expected behaviour, not a fault. You have three options:
- Rerun it. Select the top-left cell of the existing output, open Data → Consolidate (it remembers your references), and click OK. The output is overwritten.
- Link it. Tick Create links to source data when you run it. Excel then writes formulas to the sources, grouped in an outline, and the figures update when the sources change. The option is not available when the sources are on the same sheet as the output, and linked output does not pick up new categories; rerun for those.
- Replace it with formulas. For a fixed layout,
=SUM(Start:End!B5)or a SUMIFS per sheet updates automatically. For changing lists, use Power Query.
The standard set-up is covered in using the Consolidate function; this page focuses on what goes wrong.
Rows ignored because labels don't match
When consolidating by category (Left column ticked), Excel groups rows with the same label. Labels that look the same but are not create separate lines or, if you expected one line, appear to be ignored.
| Label in source | Sheet | Amount | Problem |
|---|---|---|---|
| Widgets | Jan | 400 | None |
| Widgets | Feb | 350 | Trailing space |
| Widget | Mar | 300 | Spelling |
Instead of one Widgets line of 1,050, the output has three lines (400, 350 and 300), or the 350 and 300 sit lower down where nobody looks. Fix the labels at the source: TRIM in a helper column, Find and Replace for spelling, or Data Validation lists so new entries can't drift. Testing the labels with =EXACT(A2, "Widgets") or =LEN(A2) shows hidden spaces.
Rows ignored because the range is too small
Consolidate stores each reference as a fixed address, such as Jan!$A$1:$D$120. When March has 140 rows, rows 121–140 are left out. Fixes:
- Use a named range that grows, for example a name defined as
=Jan!$A$1:INDEX(Jan!$D:$D, COUNTA(Jan!$A:$A)), and type the name in the Reference box. - Use generous fixed ranges (A1:D5000), then check the output for a stray blank category line created by the empty rows.
- Check the All references list after anyone adds rows, and update the ranges.
Consolidate's Reference box does not reliably accept structured table references such as Table1[#All] in every Excel version. A defined name that points at the table range is a dependable workaround.
Other reasons rows don't count
| Symptom | Cause | Fix |
|---|---|---|
| Output has no labels, values in the wrong rows | Top row / Left column not ticked, so it consolidated by position | Tick both when sources differ in order |
| Some values missing from totals | Numbers stored as text (green triangle) | Convert with Data → Text to Columns → Finish, or VALUE |
| Row of zeros | Function set to Count Numbers or wrong function | Choose Sum in the Function box |
| #REF! after reopening | Source workbook moved or renamed | Data → Edit Links (Workbook Links) and change source |
| A whole sheet missing | Reference never added or deleted by mistake | Re-add it in All references |
A worked check
After fixing labels and ranges, compare the output with control totals from each sheet:
| Sheet | Rows | Sum of Amount |
|---|---|---|
| Jan | 118 | 41250 |
| Feb | 126 | 43980 |
| Mar | 140 | 47310 |
| Expected | 384 | 132540 |
| Consolidate output | 132540 |
If the output were higher than expected instead, you're likely counting something twice; see why totals double after consolidating.
When to stop using Consolidate
Consolidate is fine for a few sheets with stable layouts. If you rerun it every month, add sheets often or fight label mismatches, a refreshable method is better: a consolidation worksheet template with SUMIFS, a pivot table over a combined table, or Power Query. For ad-hoc questions across many workbooks, Analistable reads the files in your browser and lets you ask for the consolidated figures in plain English; it reports results but does not write back to your workbooks. See the consolidation guide for all the options.
Consolidate across workbooks
Consolidate can read ranges in other workbooks. Open the source files while you set it up, then use Browse or click into each file to add the reference. Common problems in this case:
- The output stops updating because the links point at closed files and automatic link updates are disabled. Use Data → Refresh All or update links when prompted on opening.
- A source file was renamed or moved, so the stored reference fails. Change the source under Edit Links (Workbook Links in Microsoft 365) or remove and re-add the reference.
- Files on OneDrive or SharePoint may be referenced by a web address rather than a drive path, which can break when another person opens the summary. Keep the summary and its sources in the same synced folder, or use Power Query instead.
If you consolidate many workbooks every month, a Power Query folder combine followed by a pivot table removes most of these problems: new files are picked up on refresh and labels can be cleaned in one step.
One more habit helps: write the date and the list of sources you consolidated next to the output each time you rerun it. Because Consolidate output does not change on its own, a dated note makes it obvious when the summary is stale, which is the most common complaint behind the phrase “Consolidate does not update”.
Frequently asked questions
- Why doesn't Excel Consolidate update automatically?
- Without Create links to source data, Consolidate pastes static values. Rerun it, tick the links option, or use formulas or Power Query instead.
- Why is Create links to source data greyed out?
- It is unavailable when the source ranges are on the same worksheet as the consolidation output. Put the output on its own sheet.
- Why does Consolidate ignore some rows?
- Usually the labels differ slightly, the stored reference ends before the new rows, or the numbers are stored as text.