Advanced PivotTables

Group real dates and preserve the year

A monthly report often spans more than one year. Grouping by Months alone can combine January 2025 with January 2026. Keep Years alongside Months when the question is a chronological trend, and use an unambiguous label such as Jan 2026.

1. Start with one row per order line and real numeric Excel dates.
2. Insert a PivotTable with Date in Rows and Sales in Values.
3. In desktop Excel, right-click a date label and choose Group.
4. Select Years and Months, then inspect the resulting groups.
5. Confirm the total still reconciles to the source.

If grouping is unavailable or fails, inspect blanks, text dates and errors. Do not assume changing the cell's displayed date format converted text into real dates.

Show an amount and its share together

Suppose a period's regional Sales totals are East 300 and West 200. Total Sales is 500 and East's share is 60%.

1. Put Region in Rows.
2. Add Sales to Values twice and confirm both summarize by Sum.
3. Keep the first as the amount.
4. For the second choose Value Field Settings → Show Values As → % of Grand Total.
5. Rename it Sales share and apply a percentage format.

The denominator changes with the report context. If a filter leaves East only, East's percentage of the visible grand total becomes 100%. That may be correct for the view but is different from share of the original unfiltered total.

Make filters visible with slicers

A slicer provides buttons for a field such as Region. Select the PivotTable, use PivotTable Analyze → Insert Slicer and choose the field. Move the slicer near the report and show its active selection clearly.

To control several PivotTables, use the slicer's Report Connections or PivotTable Connections command and select compatible PivotTables sharing the same source/cache. Copying an existing PivotTable is often a practical way to create related views. Test every target; a nearby chart does not prove it is connected.

Use a timeline where supported for date selection. Clear all filters before reconciling totals, then reapply a selection and confirm the expected records contribute.

Calculated fields and ratios need care

For a traditional worksheet-source PivotTable with numeric Sales and Cost fields, a simple calculated field can express =Sales-Cost. In desktop Excel, use PivotTable Analyze → Fields, Items & Sets → Calculated Field, name it Profit and enter that expression.

This expression uses field names, not A1 references. A classic calculated field operates on aggregated field values; it is not a general row-by-row worksheet formula. For calculations such as quantity × a varying price, calculate the line amount in the source before summing, or use a suitable Data Model measure.

For margin, divide total profit by total sales. One order has profit 20 on sales 100, another profit 90 on sales 900. Overall margin is 110/1,000=11%, not the 15% simple average of 20% and 10%.

Refresh and verify the complete report

After adding source records, refresh the PivotTable. A source Excel table can expand with added rows, but an existing PivotTable still needs a refresh to include the updated data. A fixed source range may need adjustment too.

Check grand totals, groups, slicer selections, number formats and any ratio denominator after refresh. Leave enough room for PivotTables to grow without overlapping. Distinguish a repeated source subtotal from genuine transaction records before building the PivotTable.

Grouping, calculated fields, slicers and timelines vary across Windows, Mac and web. The step-by-step authoring here targets desktop Excel and classic worksheet-source PivotTables; Data Model calculations use a different approach in the next lesson. Browser exercises check interpretation and arithmetic rather than creating a native PivotTable.