13.3 PivotTables that Feed the Dashboard
Steps in Excel
- Click in
tblOrders› Insert › Tables › PivotTable › Existing Worksheet ›Calc!A3. Name it: PivotTable Analyze › PivotTable › PivotTable Name ›pvtTrend. pvtTrend: Rows = Order Date (grouped by Days only for one month, or Months for a year – Module 7.5); Values = Sum of Amount. Filter = Status: Delivered.- Copy the pivot (select it › Ctrl + C › paste at
Calc!E3) so it shares the same cache, rename itpvtCity: Rows = City, Values = Sum of Amount, sort Largest to Smallest. - Repeat for
pvtCategory(Rows = Category),pvtPlatform(Rows = Platform) andpvtKPIatCalc!I3(no Rows; Values = Sum of Amount, Count of Order ID, Average of Delivery Mins; Filter = Status: Delivered). - On every pivot: Design › Layout › Grand Totals › Off for Rows and Columns where a chart uses it (charts should not plot the Grand Total), and set the number format with right-click › Number Format….
- PivotTable Analyze › PivotTable › Options › Layout & Format › untick Autofit column widths on update so the Calc sheet does not jump on every refresh.
Worked example – pvtCity (Delivered only).
| City | Net Sales |
|---|---|
| Pune | 18,42,500 |
| Nagpur | 9,86,300 |
| Nashik | 7,12,400 |
| Sambhaji Nagar | 6,05,900 |
| Kolhapur | 4,38,700 |
| Solapur | 3,64,200 |
Because all pivots come from the same tblOrders and the same cache, a single Data › Queries & Connections › Refresh All (Ctrl + Alt + F5) updates all of them.
Ravindra Bagale's Tip
Many students do Insert › PivotTable again for every pivot and pick different source ranges – then a single slicer can't control all the pivots. Build the first pivot, copy-paste it, and then change the fields; all of them must have tblOrders as the source. And give every pivot a meaningful name (pvtCity), not PivotTable3.
Ravindra Bagale's Tip – मराठी
बरेच students प्रत्येक pivot साठी पुन्हा Insert › PivotTable करतात आणि वेगवेगळ्या source ranges निवडतात – मग एकच slicer सगळे pivots control करू शकत नाही. पहिला pivot बनवून त्याची copy-paste करा आणि मग fields बदला; सगळ्यांचा source tblOrders च असला पाहिजे. आणि प्रत्येक pivot ला अर्थपूर्ण नाव द्या (pvtCity), PivotTable3 नाही.
Ravindra Bagale's Tip – हिंदी
बहुत से students हर pivot के लिए दोबारा Insert › PivotTable करते हैं और अलग-अलग source ranges चुनते हैं – फिर एक slicer सारे pivots को control नहीं कर पाता. पहला pivot बनाकर उसे copy-paste करो और फिर fields बदलो; सबका source tblOrders ही होना चाहिए. और हर pivot को मतलब वाला नाम दो (pvtCity), PivotTable3 नहीं.
Practice task
Build pvtTrend, pvtCity, pvtCategory, pvtPlatform and pvtKPI on the Calc sheet. Check with PivotTable Analyze › Data › Change Data Source that all five point to tblOrders.