Consolidate Department Sheets by Matching Labels in LibreOffice Calc

Data → Consolidate can combine summary tables by matching their labels, even when rows and columns are in different orders. Include the labels in each source range and enable both label options. Combining by position would answer the wrong question for the tables below.

Build two department summaries

Use desktop Calc, the standard menu bar and a new ODS workbook. Current first-party Help was checked directly as LibreOffice 26.8 Help on October 19, 2026. This worked dataset has independent expected-result checks; native consolidation and save/reopen were not run.

Create sheets North, South, Snapshot and Linked. Enter this table in North.A1:C4:

Item	January	February
Paper	100	120
Ink	80	90
Labels	20	30

Enter this differently ordered table in South.A1:C4:

Item	February	January
Ink	50	40
Paper	70	60
Folders	25	15

The six numeric entries on North sum to 440; those on South sum to 260. The combined total is 700. January totals 315 and February 385. These are summary tables, so do not insert a department grand-total row inside either source range.

Labels identify the dimension, not just decorative captions. Here Paper and Ink occur on both sheets, while Labels and Folders occur on only one. The reversed month columns make a useful check that column matching is actually enabled.

1. Consolidate by both sets of labels

1. Activate Snapshot and choose Data → Consolidate.
2. Select Sum as the function.
3. In Source data range, select $North.$A$1:$C$4, then click Add. Repeat for $South.$A$1:$C$4.
4. Inspect the consolidation list: it must contain exactly those two ranges, once each. Typing a source without adding it does not put it in the list.
5. Set Copy results to to $Snapshot.$A$1. Keep the destination sheet empty so the output cannot overwrite unrelated work.
6. Expand Options. Under Consolidate by, select Row labels and Column labels. Leave Link to source data off for this first result.
7. Confirm.

The Consolidate dialog reference identifies the source list and output address. The label options control matching instead of positional alignment.

2. Verify every item, not only the grand total

Match by label rather than assuming a particular output row order:

Item	January	February	Row total
Paper	160	190	350
Ink	120	140	260
Labels	20	30	50
Folders	15	25	40
Independent totals	315	385	700

The Row total and Independent totals columns/row above are checks to calculate yourself; do not assume Consolidate adds them automatically. Paper/January must be 100 + 60 = 160, not 100 + 50 = 150. Ink/February must be 90 + 50 = 140. Those two checks expose positional mistakes that a correct 700 grand total might conceal.

Labels and Folders are legitimate one-department items, not missing-data errors. They should each appear once. If the same item appears twice under slightly different text, inspect the source labels before deleting any output row.

3. Reproduce and fix a mismatched label

In a disposable copy, change South.A3 from Paper to Paper stock. Run the same unlinked consolidation into a fresh empty sheet. Now the original Paper row should show North’s 100 and 120; a separate Paper stock row should show South’s 60 and 70. The grand total is still 700, but the intended category is split.

Restore South.A3 to Paper and consolidate into another clear destination, or clear the entire previous output area before rebuilding Snapshot. Reusing a result area that used to be larger can leave stale rows outside the new result; compare the complete output footprint.

Do not rely on automatic trimming, case normalization or spelling correction. The consolidation guide requires matching labels and explains that unmatched labels create additional output categories. Standardize source names explicitly when they are supposed to represent the same thing. Keep genuinely different categories separate.

4. Compare snapshot and linked output

Restore the original source tables. Run the consolidation again, this time into $Linked.$A$1 with Link to source data enabled. Use the same two source ranges and both label options. Linked output uses source references and can include hidden detail rows under an outline, so leave much more space than the four-row summary alone suggests.

Change North.B2, Paper’s January amount, from 100 to 130. With automatic calculation enabled, Linked should show Paper/January 190. Its January total should be 345 and overall total 730. The unlinked Snapshot should still contain Paper/January 160 and overall 700 until you rebuild it. Inspect a linked output formula and expand its outline if needed to understand which source cells feed it.

Restore North.B2 to 100. Linked should return to its original result, while Snapshot was never supposed to change. Save, close and reopen the ODS, then compare the source ranges, labels and both result types. These are acceptance tests to perform in Calc, not evidence that this article ran them natively.

Refresh limits and recovery

A linked consolidation tracks referenced cells; it is not a promise that an appended source row or newly introduced category will extend its configured ranges. To include a new North row, reopen the dialog, replace the old North source range with the enlarged range, and rebuild into a clear destination. Check the source list carefully so you do not keep both the original and overlapping expanded ranges and count North twice.

If the output looks wrong, undo the consolidation immediately or discard the disposable destination sheet. Leave the department inputs intact while diagnosing it. Common causes are a missing Add click, omitted headers, one label option disabled, duplicated source ranges or a destination that overlaps the sources.

Use this method for compatible summaries with consistent units and matching meanings. A column titled January cannot safely combine dollars with hours just because its label matches. For row-level analysis across transactions, first arrange one consistent source table and use a pivot table instead.