13.8 Refresh, Finishing Touches and a Checklist
Steps in Excel
- Adding new data: paste new rows directly below
tblOrders– the Table expands automatically. - Refresh: Data › Queries & Connections › Refresh All (Ctrl + Alt + F5). Pivots, PivotCharts, cards and slicers update.
- Refresh on open (optional): PivotTable Analyze › PivotTable › Options › Data tab › tick Refresh data when opening the file.
- Optional one-click button: a tiny macro
Sub RefreshDashboard(): ThisWorkbook.RefreshAll: End Subassigned to a shape (Module 12.5) – save as.xlsm. - 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.
- 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
Many students finish a dashboard but never cross-check it even once – and in the meeting a card's number turns out to be wrong. For every KPI, keep a manual SUMIFS/COUNTIFS check formula on the Calc sheet, and after refreshing see whether both match. Once trust is lost, nobody uses the dashboard.
Ravindra Bagale's Tip – मराठी
बरेच students dashboard पूर्ण करतात पण एकदाही cross-check करत नाहीत – आणि meeting मध्ये card चा आकडा चुकीचा निघतो. प्रत्येक KPI साठी एक manual SUMIFS/COUNTIFS check formula Calc sheet वर ठेवा आणि refresh नंतर दोन्ही match होतात का ते बघा. विश्वास (trust) एकदा गेला की dashboard कोणीच वापरत नाही.
Ravindra Bagale's Tip – हिंदी
बहुत से students dashboard पूरा कर लेते हैं पर एक बार भी cross-check नहीं करते – और meeting में card का आँकड़ा गलत निकलता है. हर KPI के लिए एक manual SUMIFS/COUNTIFS check formula Calc sheet पर रखो और refresh के बाद देखो कि दोनों match होते हैं या नहीं. भरोसा (trust) एक बार गया तो dashboard कोई इस्तेमाल नहीं करता.
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
tblOrdersvar. - 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.