Consolidate monthly sheets by position or labels

Quick answer

Use Data > Analyze > Data consolidation with SUM. Position-based consolidation requires the same categories in the same order; label-based consolidation matches the included labels instead. Both produce fixed results, so rerun the consolidation after changing a source value.

Prepare monthly sources with a deliberate mismatch

Use paid PlanMaker 2024 on Windows with the English ribbon. Make a disposable workbook with tabs Jan, Feb, and Summary. Keep all input values synthetic and reserve empty space on Summary for the output, because applying a consolidation can overwrite cells in the target range.

On Jan, enter A1 Category, B1 Amount, A2 Rent, B2 100, A3 Supplies, and B3 20. On Feb, use the same headers but put Supplies, 30 in row 2 and Rent, 110 in row 3. The reversed order is intentional.

The correct combined category totals are Rent 210 and Supplies 50, with an overall check of 260. Jan contributes 120 and Feb 140. Keep these independent checks in your notes before opening the consolidation dialog. A grand total alone will not catch every category-matching mistake.

Consolidate by labels first

1. Activate Summary and choose Data > Analyze > Data consolidation.
2. In Source ranges, enter Jan!A2:B3 and click Add.
3. Add Feb!A2:B3 in the same way. Each range includes both the labels and their adjacent numbers.
4. Set Target range to A2 on Summary, leaving the surrounding cells empty.
5. Choose SUM as the function.
6. Enable Labels in left column. Leave Labels in top row off because these selected ranges start with records, not headings.
7. Enable Sort labels if you want predictable label order, then click Apply.

Inspect the results by category text. Rent should be paired with 210 and Supplies with 50. If you enabled sorting, do not confuse alphabetic output order with the original order on Feb. Add the two amounts independently to confirm 260.

The left-column option is meaningful only because A2:A3 is included in each source selection. Selecting B2:B3 alone and then claiming it contains left-column labels would misdescribe the range. Headers at row 1 are intentionally excluded from this example.

Prove why position matching differs

For a separate practice comparison, reserve an empty area such as E2:E3 on Summary. Reopen the dialog and replace the source entries with Jan!B2:B3 and Feb!B2:B3. Set that separate target and disable both Labels options before applying SUM.

The numeric results are 130 and 130: the first positions combine 100+30, and the second combine 20+110. Their grand total is still 260, which illustrates why checking only the grand total is insufficient. Those positions mix Rent and Supplies, so this output is not a valid category summary.

To make a legitimate positional test, first create an aligned copy of the February records with Rent 110 above Supplies 30. Use its numeric two-row range instead of the reversed original. The position results should then be 210 and 50. Do not rearrange a real source report casually just to make a method work; use labels when source ordering is allowed to vary.

This comparison changes the consolidation settings stored for Summary. Before the next section, restore the two original labeled ranges, Summary!A2 as target, SUM and Labels in left column. Check the entire source list so that an obsolete numeric range is not counted a second time.

Change a source and refresh deliberately

Change Feb!B3, the Rent amount, from 110 to 120. The correct revised totals are Rent 220, Supplies 50 and overall 270. Inspect the old labeled result before rerunning: it remains the fixed output of the earlier consolidation, not a live linked formula.

On Summary, open Data consolidation, check the restored settings and click Apply. Existing target data will be replaced. Now verify 220 and 50 by their labels. If the result is still 210, check whether you updated the intended source cell and whether Apply was actually used on the correct destination worksheet.

Restore Feb!B3 to 110 and repeat the consolidation to return to 210/50. Label a real monthly output with its source period and refresh date so readers know what state it represents.

Save and troubleshoot the configuration

Save the practice workbook as PMDX, close and reopen it, and inspect the consolidation dialog on Summary. PlanMaker stores settings per worksheet. Its help specifically notes that the target range is not retained in XLS files, whereas PMDX, PMD and XLSX support it. Always inspect the target before reapplying, especially after a format conversion.

Unexpected extra categories usually call for inspecting labels and selected ranges. Different spellings or stray characters may represent different labels; clean the source deliberately rather than silently merging distinct categories. Inflated totals suggest a duplicate source range or a source that already includes a subtotal. An overwritten note indicates inadequate reserved output space, so restore your checkpoint and choose a safe target.

No native consolidation was executed during authoring.

Official references

References: position matching, label matching, and updating stored consolidations.