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 | ✓ |
About this data: Teaching sample for a fictional shop “Pearl Textiles”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on refresh finishing touches and a checklist.
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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of refresh finishing touches and a checklist on a blank sheet for CityBasket Retail.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Meera Joshi and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Liam Murphy.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply refresh finishing touches and a checklist to a 5-row made-up CityBasket Retail table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with refresh finishing touches and a checklist.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as CityBasketR_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
