Ravindra BagaleCourses & study guides

7. PivotTables and PivotCharts

7.9 Report Connections, Refresh and Data Source

By Last updated:

One slicer can control several PivotTables that use the same source (the same pivot cache).

Steps in Excel

  1. Build two pivots from tblOrders (Sales by City, Orders by Category).
  2. Select the City slicer › Slicer › Slicer › Report Connections (or right-click the slicer › Report Connections…) › tick both PivotTables › OK. Timelines have the same option.
  3. Refresh one pivot: PivotTable Analyze › Data › Refresh (Alt + F5). Refresh everything: Data › Queries & Connections › Refresh All (Ctrl + Alt + F5).
  4. Auto refresh on open: PivotTable Analyze › PivotTable › Options › Data tab › tick Refresh data when opening the file.
  5. Source moved or is a range? PivotTable Analyze › Data › Change Data Source › point to tblOrders.
  6. Keep column widths after refresh: Options › Layout & Format › untick Autofit column widths on update.

Ravindra Bagale's Tip

Source data badalla tari PivotTable swatah update hot nahi – refresh karava lagto. Khup students juna aakda manager la pathavtat. Report pathvaychya aadhi nehmi Refresh All kara, aani option madhe Refresh on open tick kara. Report Connections madhe pivot disat nasel tar to vegalya source/cache varun banavla aahe – same Table varun punha banva.

Practice task

Connect one City slicer and one timeline to three PivotTables. Add new rows to tblOrders and use Refresh All. Turn on Refresh on open.