3.2 PivotTables beyond the basics

What you'll be able to do

Make PivotTables answer comparing questions: share of a parent group, running total, rank, and growth against last month. Drive several PivotTables from one set of buttons, which is most of the way to a dashboard.

Before you start

Carry on in the pivot sales workbook, with the basic PivotTable from the previous lesson in place.

The work

1. Same either way: Show Values As is where comparing lives. Open Value Field Settings on your Amount field and switch to the Show Values As tab. Work down the list. % of Parent Row turns a city-inside-region layout into each city's share of its region. Running Total In, by month, turns monthly sales into the year so far. Rank Largest to Smallest turns amounts into positions. Difference From, with the previous month, turns levels into growth. Same data, four questions, answered from a dropdown.
2. Same either way: add the same field to Values twice, once as a plain Sum and once as % of Grand Total. The report then shows the amount and the share beside each other, which is how an honest report states a number.
3. No documented keyboard shortcut for this — use the menu: a calculated field works out a new number from the PivotTable's own totals. Go to PivotTable Analyze → Fields, Items & Sets → Calculated Field. Name it Margin, write a formula over existing fields such as =Profit/Amount, and press Enter. Use these for simple ratios. Anything bigger belongs in the data itself, or later in the data model.
4. No documented keyboard shortcut for this — use the menu: slicers are filter buttons a reader can press. Go to PivotTable Analyze → Insert Slicer and tick Store and Month. Timelines do the same for dates, with a bar you drag to pick a period. Insert Timeline puts one in.
5. Same either way: one set of slicers can drive several PivotTables. Open the slicer's context menu with Shift+F10, choose Report Connections, and tick every PivotTable it should control. Two PivotTables and one connected slicer set is already an interactive report, and the dashboards module builds straight on this.
6. Same either way: when a formula outside a PivotTable must read a number inside it, type = and click the cell. Excel writes GETPIVOTDATA, which keeps pointing at "the total for Hyderabad" even when the PivotTable is rearranged. A plain cell reference would grab whatever lands in that spot tomorrow. That difference is what makes it safe to build a formatted report on top of a PivotTable.

Try it

Build these: sales with amount and share side by side, a running total by month, a rank of stores, and growth against the previous month. Then make two PivotTables with one slicer set driving both, and one GETPIVOTDATA cell reading a total into a sentence such as "Hyderabad sold ₹—".

Check yourself