Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!


 

Link pivot table filters

One of the drawbacks of pivot tables (previously) was that if you built many pivot tables in the same workbook to create dashboards, it was difficult to set up a control sheet to change a particular filter on all your pivots. For example to change all the pivots to only show a certain division. In order to link pivot table filters you needed to use VBA or record a macro.

However it is now possible to achieve the same thing using the slicer options.

You can connect a single Slicer to multiple report to create interactive dashboards. This is useful if you want all the pivot tables in a spreadsheet to change when you click on the slicer. The method to do this (once you have set up some slicers):

  • Click on the Slicer that you want to use to control multiple pivot tables or pivot charts
  • Click on the SLICER ribbon items and click Report Connections (shown below)
  • Tick all the Pivot Tables (and/ or Pivot Charts) that you want to be connected and click OK.
link pivot table filters

Now whenever you change the Destination Network Slicer in the example above, both Pivot Tables will change to reflect your choice.

Grouping items in pivot table slicers