Group and calculate PivotTable results in Google Sheets

Quick answer

Use a date group on the pivot’s date labels, then choose a percentage under Values > Show as. A calculated field can express revenue per order without adding an average of already averaged groups. This exercise distinguishes those three operations.

Prepare a dated fixture

Use desktop Google Sheets with edit permission and a disposable workbook. This is an ordinary Sheets pivot, not Connected Sheets. On a tab named Sales, enter A1:C5:

Date	Order	Revenue
2026-01-05	A01	100
2026-01-20	A02	200
2026-02-03	A03	300
2026-02-18	A04	400

Enter actual dates, not ambiguous date strings: if necessary use the date picker or DATE formulas, then format the cells as dates. Revenue must be numeric. The independent checks are January 300, February 700, total 1,000, four orders.

Select A1:C5, choose Insert > Pivot table, and put the practice pivot on a separate sheet. Add Date under Rows and Revenue under Values, summarized by SUM. Remove any unwanted suggested fields. Keep totals visible. This compact setup is only the starting point; the task is to change the pivot’s level of detail and calculation.

Group dates and calculate shares

1. In the pivot, right-click a date label, choose Create pivot date group, and select the month period.
2. Check that the two January rows form one 300 group and the February rows form one 700 group. This one-year fixture deliberately avoids combining the same month from different years.
3. Click the pivot’s Edit control. In Revenue’s Values settings, change Show as from Default to the grand-total percentage option.
4. Read the results as 30% and 70%, with 100% overall. Changing the display to percent formatting alone would not perform this calculation.
5. Return Show as to Default before the next calculation, so revenue remains directly comparable with the source.

For multi-year reporting, group by a year-aware period or include year separately. A label such as January without a year can hide an unintended combination.

Add a revenue-per-order metric

Under Values > Add > Calculated field, name the field Revenue per order. Enter =SUM(Revenue)/COUNTA(Order), select Custom under Summarize by, and add the field after syntax validation succeeds.

Expect January 150, February 350, and the grand-total calculation 250. The denominator is the number of populated Order records, not distinct customers. Keep Order filled for every valid row; blank IDs would produce a misleading denominator.

Change one input, then reset

Change Sales!C2 from 100 to 300. The pivot’s referenced source cells refresh: January becomes 500, February stays 700, total becomes 1,200. Revenue per order becomes 250, 350, and 300 respectively. If you temporarily switch revenue back to grand-total percentages, expect about 41.67% and 58.33%. Restore C2 to 100 and Show as to Default afterward.

Rows appended outside A1:C5 are not included merely because the pivot refreshed. Expand the source range deliberately before testing appended records.

Diagnose and keep the result

If date grouping is unavailable, inspect the source for text dates or blank/non-date values. If SUM becomes a count, inspect numeric types. If a changed category disappears, inspect pivot filters: filters by value preserve their selected list and may need new items enabled. To undo grouping, right-click the grouped label and use Ungroup pivot items.

Wait for saving and reopen the practice workbook to inspect its source range, grouping and formula. Do not infer a successful live refresh from these calculated expectations. These steps were documentation-reviewed; no native pivot execution or screenshots were produced.

Official references

• Create & use pivot tables - Computer - Google Docs Editors Help
• Customize a pivot table - Computer - Google Docs Editors Help