Ravindra BagaleCourses & study guides मराठी Track your progress

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

About this data: Teaching sample for a fictional shop “Pearl Textiles”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on pivottables that feed the dashboard.

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.

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.

Loading practice tasks...

  1. Total sales (Easy): Add up Sales (₹) in C2:C11. (sheet "Data")
  2. Total target (Easy): Add up Target (₹) in D2:D11. (sheet "Data")
  3. West rows (Easy): How many rows have Region "West"? (sheet "Data")
  4. Max sales (Easy): Largest Sales (₹) in C2:C11. (sheet "Data")
  5. Total orders (Easy): Add up Orders in F2:F11. (sheet "Data")
  6. West sales (Medium): Total of Sales (₹) where Region is "West". (sheet "Data")
  7. Avg margin (Medium): Average of Margin % in E2:E11, rounded to 1 decimal. (sheet "Data")
  8. Margin over 18 (Medium): How many rows have Margin % > 18? (sheet "Data")
  9. Sales of Central (Medium): Sales (₹) for Region "Central". (sheet "Data")
  10. Sales / Target (Hard): Total sales / total target, 3 decimals. (sheet "Data")
  11. Above avg sales? (Hard): "High" if C2 > average sales else "OK". (sheet "Data")
  12. South orders (Hard): Total of Orders where Region is "South". (sheet "Data")

See all practice tasks in the Task Hub →

Worked example — pivottables that feed the dashboard

Why

Aarti Kulkarni in Pune and Emma Clarke abroad both need a clear, repeatable way to use pivottables that feed the dashboard on a BrightLedger Finance sheet without breaking totals.

What

Pivottables That Feed The Dashboard is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for pivottables that feed the dashboard.
  2. Enter a small made-up value for Aarti Kulkarni using $ where money appears.
  3. Add a second value for Emma Clarke using ₹ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for BrightLedger Finance's sample row.
Meaning: Once pivottables that feed the dashboard is correct on one row, copy the same pattern down the column.

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Pivottables That Feed The Dashboard?
Card 1: What is Pivottables That Feed The Dashboard? · Download PNG
Revision card 2: Tiny example — Pivottables That Feed The Dashboard
Card 2: Tiny example — Pivottables That Feed The Dashboard · Download PNG
Revision card 3: Checklist — Pivottables That Feed The Dashboard
Card 3: Checklist — Pivottables That Feed The Dashboard · Download PNG