7. PivotTables and PivotCharts
7.11 PivotCharts
Steps in Excel
- Click the PivotTable › PivotTable Analyze › Tools › PivotChart › choose Clustered Column › OK. (Or Insert › Charts › PivotChart directly from the Table.)
- The chart follows the pivot: changing fields, filters or slicers changes both.
- PivotChart Analyze › Show/Hide › Field Buttons – hide the grey buttons for a clean dashboard look.
- Move to its own sheet: Design › Location › Move Chart.
- Chart types not supported as PivotCharts include scatter, histogram, box & whisker, treemap, sunburst, waterfall and map – for those, use normal charts on a formula summary (Module 8).
Worked example. A column PivotChart of monthly sales by platform (Months grouped, Platform in Legend) with the City slicer connected – Salman shows Pune's September Ganeshotsav peak in the weekly review.
Ravindra Bagale's Tip
Many students build a dashboard leaving the field buttons on the PivotChart – it looks very cluttered. Hide the Field Buttons, write a clear title and use slicers. And if the pivot has too many rows (100 products), the chart can't be read at all – apply a Top 10 filter first.
Ravindra Bagale's Tip – मराठी
PivotChart वर field buttons तसेच ठेवून बरेच students dashboard बनवतात – ते खूप cluttered दिसतं. Field Buttons लपवा, title स्पष्ट लिहा आणि slicers वापरा. आणि pivot ला जास्त rows (100 products) असतील तर chart वाचताच येत नाही – आधी Top 10 filter लावा.
Ravindra Bagale's Tip – हिंदी
PivotChart पर field buttons वैसे ही छोड़कर बहुत से students dashboard बना देते हैं – वह बहुत cluttered दिखता है. Field Buttons छुपाओ, title साफ़ लिखो और slicers इस्तेमाल करो. और pivot में बहुत ज़्यादा rows (100 products) हों तो chart पढ़ा ही नहीं जाता – पहले Top 10 filter लगाओ.
Practice task
Create a PivotChart of sales by month and platform, hide field buttons, connect the City slicer and add a meaningful chart title.
Thodkyaat sangaycha tar (quick recap)
Clean Table var PivotTable; Rows/Columns/Values/Filters samja; Sum vs Count heading nakki bagha; % aani running total sathi Show Values As; dates group kara (Years sobat); calculated field sum var chalto; Top 10, slicers, timelines aani Report Connections ne interactive report; source badalla ki Refresh All; KPI sathi GETPIVOTDATA. Aata pudhe jaauya – charts!