ONLYOFFICE Spreadsheet Editor · Pivot tables and analysis · Intermediate
Group ONLYOFFICE Pivot Dates and Compare Totals
Check month-and-year groups, numeric bands, percentage shares and a running total with an eight-record ONLYOFFICE pivot exercise.
Documentation and independent data checks reviewed; native execution not performed · Editorial standards
Quick answer
Keep the year when grouping dates from more than one year. First reconcile the ordinary Sum of Amount; only then change how that sum is displayed. A percentage or running total answers a different question from the original sum.
This is an advanced follow-on to pivot-table basics, not a replacement for its source-range and refresh lesson. The workflow targets the free Desktop Editors 10.0.0 English interface using the current official help. The desktop changelog records pivot Group/Ungroup by 6.3.1, but that does not establish every present-day comparison option in older versions. No grouping, percentage display, screenshot or save/reopen test was performed in a native application for this article.
Download and validate the source
Download the original eight-record practice CSV. It contains fictional data, not an exported pivot or an XLSX template. Import it as UTF-8, comma-separated data, name the sheet Sales, and save a working copy as XLSX. The source occupies A1:C9:
| Date | Units | Amount |
|---|---|---|
| 2025-01-05 | 1 | 40 |
| 2025-01-20 | 2 | 60 |
| 2025-02-05 | 3 | 90 |
| 2025-02-20 | 4 | 110 |
| 2026-01-05 | 1 | 50 |
| 2026-01-20 | 2 | 70 |
| 2026-02-05 | 3 | 80 |
| 2026-02-20 | 4 | 100 |
The Date column in the CSV is ISO text. Confirm that the imported cells are actual dates before grouping. Formatting a text cell to look like a date is not sufficient. For an unambiguous repair in a copy, enter the corresponding =DATE(2025,1,5) style expressions in column A, with each row’s actual year, month and day, then use date formatting. The DATE guide explains the arguments.
Before building a pivot, =COUNT(A2:A9) and =COUNT(C2:C9) should both be 8 after date conversion, and =SUM(C2:C9) should be 600. These are independent target checks, not recorded application results. Keep all filters cleared for the first comparison.
Keep January in each year separate
Create a pivot from Sales!$A$1:$C$9, with Date in Rows and Amount in Values, summarized by Sum. On a date item, use the context menu’s Group and select Years and Months. Check the starting and ending dates cover all eight records. The official grouping instructions describe the available periods.
Compare these four year/month combinations rather than relying on a particular visual label or layout:
| Year and month | Expected Amount |
|---|---|
| 2025 January | 100 |
| 2025 February | 200 |
| 2026 January | 120 |
| 2026 February | 180 |
| Grand total | 600 |
For example, 2025 January is 40 + 60. Both years total 300, but the monthly distribution changes. A report grouped only by month can combine January into 220 and February into 380. Those values may suit a seasonal comparison, but they cannot answer which year’s January improved.
Compare shares and a running total
In the Amount value-field settings, retain Sum as the aggregation and choose Show values as → % of grand total. Display two decimal places. The four amounts above represent 16.67%, 33.33%, 20.00% and 30.00% of 600. Calculating 100 / 600 independently confirms the first percentage; changing number formatting alone does not perform that division.
For a separate running-total exercise, restrict the report to 2026, leaving January and February in chronological order. Choose Running total in, with the month field as Base field. The expected accumulated amounts are 120 and 300. For % running total in, the corresponding cumulative shares are 40% and 100% of the filtered 300. The two ordinary monthly amounts are still 120 and 180. Confirm the selected base field and filter before interpreting the result.
The 10.0 source’s value-field controls distinguish these display calculations from the aggregation. This source inspection is not proof that a specific installed desktop package produced the expected output.
Return Show values as to No calculation, then clear the year filter. Check 600 again before the next exercise. Do not compare a filtered running total with an unfiltered grand total.
Group numeric units into bands
Use a separate pivot with Units in Rows and Amount summarized by Sum. Group a Units item with Starting at 1, Ending at 4, and By 2. For these integers, the intended bands are 1–2 and 3–4:
- Units 1–2: 40 + 60 + 50 + 70 = 220.
- Units 3–4: 90 + 110 + 80 + 100 = 380.
The bands still sum to 600. This exercise does not test fractional values or values outside the specified boundaries. Use Ungroup on a grouped item to return to individual unit values. Their totals are 1: 90, 2: 130, 3: 170 and 4: 210.
Diagnose mismatches and test a change
- Dates cannot be grouped: inspect numeric date conversion, blank/error cells and the actual source boundaries. Keep the untouched CSV so you can compare all eight records.
- A total is 8: inspect Count versus Sum and numeric import. Eight records is not the sum of their amounts.
- January is 220: inspect the presence of the year grouping before concluding the data is wrong.
- A percentage seems too large: inspect filters and the denominator. In the 2026-only view, January is 120 / 300 = 40%, not 120 / 600 = 20%.
- Running totals jump backward: check month ordering, base field and which year is included. Preserve the ordinary sums as a comparison.
In a disposable copy, change C6 from 50 to 80 and refresh the affected pivots. The independent targets become grand total 630, 2026 January 150, 2026 total 330, and numeric band 1–2 250. Restore C6 to 50 and refresh to recover the original controls. Save, close and reopen the XLSX, then inspect groups, selected display calculation and filters again. Persistence is a reader check, not an outcome claimed here.
Sources and limits
- Official pivot help, reviewed 23 October 2026.
- Desktop changelog: 10.0.0 release and historical Group/Ungroup gate.
- 10.0 grouping-dialog source: period and interval controls.
- Free Desktop Editors: Windows, macOS and Linux availability. Hosted, mobile and paid-edition parity is not asserted.
The CSV has no formulas, macros, external connections or pivot definition. Its records, subtotals, percentages and changed-input controls were independently checked outside ONLYOFFICE. Native import, date recognition, grouping, display calculations and XLSX persistence remain untested for this exercise. For filtering rather than grouping, see slicers.