Ravindra BagaleCourses & study guides

13. Excel Dashboards

13.8 Refresh, Finishing Touches and a Checklist

By Last updated:

Steps in Excel

  1. Adding new data: paste new rows directly below tblOrders – the Table expands automatically.
  2. Refresh: Data › Queries & Connections › Refresh All (Ctrl + Alt + F5). Pivots, PivotCharts, cards and slicers update.
  3. Refresh on open (optional): PivotTable Analyze › PivotTable › Options › Data tab › tick Refresh data when opening the file.
  4. Optional one-click button: a tiny macro Sub RefreshDashboard(): ThisWorkbook.RefreshAll: End Sub assigned to a shape (Module 12.5) – save as .xlsm.
  5. Protect: lock everything except slicers (Module 14.1): right-click slicer › Size and Properties › untick Locked; then Review › Protect › Protect Sheet and tick Use PivotTable & PivotChart and Edit objects if slicers must work.
  6. Test with a colleague who hasn't seen it: can they answer the 5 questions from 13.1 in under a minute?

Worked example – dashboard checklist.

Check ✓
Each KPI matches a manual SUMIFS check (Net Sales = ₹49,50,000) ✓
All slicers connected to all pivots ✓
No Grand Total plotted in any chart ✓
Number formats consistent (₹ L, %, min) ✓
Data date and fictional-data note visible ✓
Fits one screen; Calc sheet hidden; structure protected ✓

Manual check: =SUMIFS(tblOrders[Amount],tblOrders[Status],"Delivered") must equal the Net Sales card when no slicer is selected.

Ravindra Bagale's Tip

Khup students dashboard finish kartat pan ekda pan cross-check karat nahit – aani meeting madhe card cha aakda chukicha nighto. Pratyek KPI sathi ek manual SUMIFS/COUNTIFS check formula Calc sheet var theva aani refresh nantar dogha match hotat ka te bagha. Vishwas (trust) ekda gela ki dashboard konich vaparat nahi.

Practice task

Add ten new fictional orders for Solapur dated 01-04-2026, refresh all, and confirm that the cards, charts and your manual SUMIFS check all change by the same amount.

Thodkyaat sangaycha tar (quick recap)

  • Aadhi audience, prashna aani KPI definitions – mag Excel.
  • Data, Calc aani Dashboard sheets vegle; sagle pivots ekach tblOrders var.
  • KPI cards = shape linked to a cell; cell = GETPIVOTDATA kiwa SUMIFS.
  • PivotCharts with field buttons hidden; insight titles; ekach highlight rang.
  • Slicers aani timeline → Report Connections → sagle pivots.
  • Refresh All, cross-check formulas, protect, aani ek screen madhe.

Aata pudhe jaauya – dashboard tayar zala, aata to surakshit kasa theva aani share/print kasa karaycha te bagha.