7. PivotTables and PivotCharts
7.9 Report Connections, Refresh and Data Source
One slicer can control several PivotTables that use the same source (the same pivot cache).
Steps in Excel
- Build two pivots from tblOrders (Sales by City, Orders by Category).
- Select the City slicer › Slicer › Slicer › Report Connections (or right-click the slicer › Report Connections…) › tick both PivotTables › OK. Timelines have the same option.
- Refresh one pivot: PivotTable Analyze › Data › Refresh (Alt + F5). Refresh everything: Data › Queries & Connections › Refresh All (Ctrl + Alt + F5).
- Auto refresh on open: PivotTable Analyze › PivotTable › Options › Data tab › tick Refresh data when opening the file.
- Source moved or is a range? PivotTable Analyze › Data › Change Data Source › point to
tblOrders. - Keep column widths after refresh: Options › Layout & Format › untick Autofit column widths on update.
Ravindra Bagale's Tip
Even when the source data changes, a PivotTable doesn't update itself – you have to refresh it. Many students send the manager old figures. Always do Refresh All before sending a report, and tick Refresh on open in the options. If a pivot doesn't appear in Report Connections, it was built from a different source/cache – rebuild it from the same Table.
Ravindra Bagale's Tip – मराठी
Source data बदलला तरी PivotTable स्वतः update होत नाही – refresh करावा लागतो. बरेच students जुने आकडे manager ला पाठवतात. Report पाठवण्याआधी नेहमी Refresh All करा, आणि options मध्ये Refresh on open tick करा. Report Connections मध्ये pivot दिसत नसेल तर तो वेगळ्या source/cache वरून बनवला आहे – same Table वरून पुन्हा बनवा.
Ravindra Bagale's Tip – हिंदी
Source data बदल जाए तब भी PivotTable अपने-आप update नहीं होती – refresh करना पड़ता है. बहुत से students manager को पुराने आँकड़े भेज देते हैं. Report भेजने से पहले हमेशा Refresh All करो, और options में Refresh on open tick करो. Report Connections में pivot न दिखे तो वह किसी अलग source/cache से बनी है – उसे same Table से दोबारा बनाओ.
Worked example — report connections refresh and data source
Why
Sana Sheikh in Pune and Anna Kowalski abroad both need a clear, repeatable way to use report connections refresh and data source on a QuickCart Delivery sheet without breaking totals.
What
Report Connections Refresh And Data Source is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for report connections refresh and data source.
- Enter a small made-up value for Sana Sheikh using ₹ where money appears.
- Add a second value for Anna Kowalski using £ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- 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).Meaning: Once report connections refresh and data source is correct on one row, copy the same pattern down the column.
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 report connections refresh and data source on a blank sheet for NovaCraft Tools.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Neha Shah 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 James O'Neill.
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 report connections refresh and data source to a 5-row made-up NovaCraft Tools 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 report connections refresh and data source.
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 NovaCraftTo_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.
