Ravindra BagaleCourses & study guides

13. Excel Dashboards

13.3 PivotTables that Feed the Dashboard

By Last updated:

Steps in Excel

  1. Click in tblOrders › Insert › Tables › PivotTable › Existing Worksheet › Calc!A3. Name it: PivotTable Analyze › PivotTable › PivotTable Name › pvtTrend.
  2. 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.
  3. Copy the pivot (select it › Ctrl + C › paste at Calc!E3) so it shares the same cache, rename it pvtCity: Rows = City, Values = Sum of Amount, sort Largest to Smallest.
  4. Repeat for pvtCategory (Rows = Category), pvtPlatform (Rows = Platform) and pvtKPI at Calc!I3 (no Rows; Values = Sum of Amount, Count of Order ID, Average of Delivery Mins; Filter = Status: Delivered).
  5. 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….
  6. 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

Khup students pratyek pivot sathi punha Insert › PivotTable kartat aani vegveglya source ranges nivadtat – mag ekach slicer sagle pivots control karu shakat nahi. Pahila pivot banvun tyachi copy-paste kara aani mag fields badla; saglyancha source tblOrders ch asla pahije. Aani pratyek pivot la arthapurna nav dya (pvtCity), PivotTable3 nahi.

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.