Microsoft Excel · Analysis and charts · Intermediate
Group dates and display percentages in a PivotTable in Excel
Group dates and display percentages in a PivotTable in Excel with a complete practice example, expected results and recovery checks.
Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards
Quick answer: Group real date values by both Years and Months, then add the Amount field twice so the PivotTable can display sums beside % of Grand Total. Formatting a sum as a percentage is not the same calculation.
Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.
Prepare a date-based source
In a new sheet named Sales, enter Date and Amount in A1:B1. Enter these date formulas in A2:A5 and amounts in B2:B5:
| Date formula | Amount |
|---|---|
=DATE(2026,1,5) |
100 |
=DATE(2026,1,20) |
200 |
=DATE(2026,2,10) |
300 |
=DATE(2027,1,5) |
400 |
Format A2:A5 as dates. Using date formulas avoids locale-dependent slash-date interpretation. The source total is 1000. January 2026 is 300, February 2026 is 300 and January 2027 is 400. Including two years deliberately exposes accidental merging of same-named months.
Create a standard worksheet-based PivotTable from Sales!A1:B5 on a new worksheet, with Date in Rows and Amount in Values summarized by Sum. Do not add the data to the Data Model for this exercise. If you need the initial field-list setup, the existing PivotTable source guide explains the foundational layout and refresh concept; its detailed interface is scoped to Excel 2013, unlike this Microsoft 365 Windows walkthrough.
Group the dates without combining years
Right-click a date item in the PivotTable and select Group. In the Grouping box, check that Starting at and Ending at include the full January 2026–January 2027 span. Under By, select Years and Months, then confirm with OK. If Excel already grouped dates automatically, inspect that layout and adjust the grouping rather than assuming it chose the required levels.
Keep Years above Months in the row hierarchy. The report should distinguish January under 2026 from January under 2027. Check the month sums against the three expected amounts before adding percentage calculations. If you see a single January total of 700 without a year distinction, the grouping is too broad for this question.
Add a percentage beside the amount
Drag Amount into the Values area a second time, below the first copy. In the PivotTable, right-click a number in the second value field and choose Show Values As → % of Grand Total. Leave the first field as its normal sum.
The month rows should now show 300 and 30% for January 2026, 300 and 30% for February 2026, and 400 and 40% for January 2027. If year subtotals are displayed, 2026 is 600/60% and 2027 is 400/40%; the grand total is 1000/100%. These percentages use the report’s grand total as the denominator, not each year’s subtotal.
Change one amount and refresh
Change Sales!B5 from 400 to 600. Refresh the PivotTable after the source edit. The grand total becomes 1200; January 2026 and February 2026 each become 25%, while January 2027 becomes 50%. The original sum field must show 300, 300 and 600, not percentages.
If percentages are unchanged, check refresh and the source address before editing the displayed report. If the second field shows 30000%, it may simply have percentage number formatting applied to a sum; use Show Values As for the actual calculation. If grouping fails, inspect blanks, errors and text masquerading as dates in the source. Save as XLSX, reopen and verify the final grouped sums and percentages. This exercise owns grouping and display calculations, not source-range expansion or PivotTable creation in every Excel platform.
Official references
Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.