Two situations come up constantly: you need totals inside a table at each change of region, rep or category, and you need several similar ranges — monthly sheets, regional exports, department workbooks — merged into one. RixSheets covers both with dedicated tools: Subtotal writes grouped totals directly into the table with outline controls, and Consolidate folds multiple ranges into a single summary using the function you pick. Neither requires you to write a formula.
Subtotal: grouped totals in place
Subtotal works on a table with a grouping column and value columns:
- Sort first by the grouping column — Region, Category, Salesperson. The tool inserts a subtotal row wherever the group value changes, so unsorted data produces garbage.
- Open Data, Subtotal.
- Choose the group column — the one you sorted by.
- Choose the function: sum, count or average.
- Tick the value columns to aggregate — Units, Amount, both.
- Confirm. Subtotal rows appear after each group, a grand total lands at the bottom, and the left margin gains outline buttons.
The outline controls are the payoff: click the top-level button to collapse everything to a one-line-per-region summary, expand to see every row again. For a printed report or a manager who wants totals where the data is, this beats sending everyone to a separate summary sheet.
Two refinements separate a tidy subtotal from a messy one. Keep the original values visible — subtotal rows sit between groups, so a reader can always trace a total back to the rows beneath it, which is exactly the auditability a separate summary sheet loses. And remember the rule is one grouping column per pass: if you need subtotals nested by region and then by rep, sort by region then rep and run the tool twice, outer group first, leaving "replace current subtotals" unticked the second time.
Consolidate: merge many ranges into one
Consolidate answers "how do the months add up?" or "what do the regions total together?":
- Open Data, Consolidate.
- Add each source range — the January block, the February block, the regional exports — one at a time.
- Choose the function: sum is the default, with count, average, maximum and minimum among the options.
- Decide how rows and columns are matched — by their labels so "Office supplies" in March lines up with "Office supplies" in April, or by position when the ranges are identical layouts.
- Run the consolidation. RixSheets writes a merged summary into the destination range.
Label-based matching is the forgiving mode: categories can appear in a different order on each sheet, and missing categories simply contribute nothing. Position-based matching is faster but assumes every range has the same shape — use it only when you know they do.
Consolidation is also the right answer for files, not just sheets: a monthly routine where each branch emails its workbook becomes a five-minute job — open each file in a tab, add its summary range to the Consolidate list, run once. The merged output is an ordinary range, so it feeds a pivot, a chart or a printed subtotal report without any further ceremony.
Choosing among Subtotal, Consolidate and pivots
| Need | Reach for |
|---|---|
| Totals inside the table, collapsible groups | Subtotal |
| Merge several ranges or sheets into one | Consolidate |
| Re-arrangeable summary with drag-and-drop fields | Pivot table |
| Interactive filtering for other readers | Pivot plus slicers |
They are not rivals. A common flow: Consolidate twelve monthly sheets into one annual table, sort it, then hand it to Subtotal for the printed grouped report — or pivot it when the questions start changing; see pivot tables in RixSheets.
Practical cautions
- Subtotal over an unsorted table is the classic mistake; sort is step one, always.
- Consolidate by label requires exact spelling — "Transport" and "Transport " are different labels, so run find and replace first; see sort, filter and dedupe.
- Both tools write results into the sheet; keep the sources untouched and re-run after corrections rather than hand-editing the summary.
One more distinction saves rework: Subtotal and Consolidate produce static results, recalculated only when you re-run them, while a pivot refreshes from its source with a click. If the summary needs to track changing data automatically, build the pivot; if the summary is a snapshot for a meeting or a printout — which is most of them — Subtotal and Consolidate are lighter and produce exactly the shape asked for.
That static quality is a feature for audit, too. A consolidated total that was correct when the meeting pack was printed stays correct on paper, even if the underlying sheets change the next morning — and the re-run after corrections takes seconds, with the trail recorded in the sheet rather than in someone's memory.
A worked example that covers both
A quarterly close pulls it all together in one sitting:
- Twelve monthly sheets sit in one workbook, identical layout, from the accounting system.
- Consolidate merges them by label into a single annual range on a summary sheet.
- Find and replace fixes the three spellings of "Transport" that crept in during March.
- The range is sorted by cost center and Subtotal adds group sums, collapsed to the outline view.
- The collapsed view prints — two pages, where the raw data was forty.
- When someone questions a number, the outline expands and the rows are right there.
Six steps, no formulas written, and every figure traceable from total to transaction. That is the standard both tools are aiming at, and why they have outlived every flashier alternative for grouped totals.
What to explore next
- Pivot tables in RixSheets — the flexible big sibling of Subtotal.
- Advanced Filter — extract exactly the rows to consolidate.
- Import and export CSV and XLSX — where those monthly ranges usually come from.