Show Slicer selection on a Graph

{ 7 Comments }

Assume a simple Sales dataset from which a Pivot Table has been created.  The Pivot Table has been sliced by two columns of the dataset.  To represent data pictorially, a Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook).  Now let’s say, a user makes a few slicer selections on the Pivot Table worksheet.  When one now clicks on the Pivot Chart worksheet, one does not see what selections were made in the slicers (which are placed on the Pivot Table worksheet).  So one has to go back and forth between the two worksheets to keep track of the slicer selections made.  One may want to view the slicer selections made on the Pivot Chart as well.  Changes made in the slicer selections should automatically reflect on the Pivot Chart worksheet.

This can be accomplished by using the PowerPivot tool and CUBE functions (available in Excel 2007 + versions).  You may download the solution workbook from here.

You may watch a short video of my solution here

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Hi Ashish,
    Can we restrict slicer selection by user?
    For example, I have a report on National level which contains sales of all states. When I send it to any respective State Teams like Orissa, they are able to see the other states sales by clicking the state slicer buttons.

    So I am thinking, is there any possibility to create an excel file with multiple user login system with restriction to users to access their specified data on different levels like state / region / zone / national.

    Please help me.

    Thanks
    Ankur