Excel Slicer for Pivot table and Grand Total: Workaround

If you create  slicer for a pivot table and you want to see details of "Grand Total"; it may give you wrong result.

e.g.

1. We have sales data as

2. And We have created Pivot table as (Without any Report Filters)

   

   Report filter:
                      None

   Column Labels:
                       Sales Person

    Values:
                      Sum of Unit sold

3. Now we insert slicer of months

4. Select Any month e.g. January

5. You can see now Grand total is 3, if you want to view details of those 3 Sales persons who sold units in January, double click on 3; but you will see all data instead of only 3 people

Excel Slicers (2010 Onwards) & Dashboad

Microsoft has introduced Slicers from Excel 2010 which is a really great feature. It is nothing but filters. It should be connected to pivot tables or data connections. Also it can be connected to multiple pivot tables (provided every pivot table has same source data). This is useful to slice same data in different views. such as Dashboards. They can be used using Excel services in SharePoint 2010 also

e.g.
Let us say we have Sales data for as Sales Person, Region, Month,Quarter and Units sold.

Requirement :
Output should have two charts

1. Sales by Month (Column Chart)
2. Sales by Region  (Pie Chart)

Both of above charts should have slice and dice facility by Sales Person and Quarter

Design:

Create Two different Pivot tables. 
1. Pivot Table 1: Structure 

   Columns: B and C
   Report filter:
                       Sales Person
                       Quarter

   Column Labels:
                       Month

    Values:
                      Sum of Unit sold

Create Pivot Chart of Above Pivot Table of type 'Column'; Hide field buttons using 'Pivot Chart Tools -> Analyse -> Field Buttons -> Hide All
Format chart as per your need.

2. Pivot Table 2: Structure 


 Columns: F & G

 Report filter:
                       Sales Person
                       Quarter

   Column Labels:
                       Region

    Values:
                      Sum of Unit sold

Create Pivot Chart of Above Pivot Table of type 'Pie'; Hide field buttons using 'Pivot Chart Tools -> Analyse -> Field Buttons -> Hide All
Format chart as per your need.




3. Insert Slicer

Select any Pivot table or Pivot Chart and add slicer
Insert -> Slicer (You must select Pivot table or Pivot Chart before inserting)

Select Field names which will be used to slice and dice

Now, you need to give connection of pivot tables to both the slicers, When you create slicer it has only one pivot table connection; to connect slicer to another pivot table right click on slicer and select 'PivotTable Connections' and select another pivot table

You can format slicers if you want by selection Slicers and then Slicer tools on Ribbon.

4. Create formulae for dynamic update of Chart Titles

Now Insert formulae next to any of the  Pivot table to display chart titles as per selection of slicer items
Formula in D3 will depict which SalesPersons  are selected
Formula in D4 will depict which Quarters  are selected
Formula in D5 will depict which Quarter and SalesPersons  are selected

Assign D5 value to chart title.: Select Chart Title then go to Formula bar and type '=' and the select D5 cell

Press Enter button Repeat this step to other chart as well





5. Finalize Dashboard

Move Charts & Slicer to Column I, Format Charts and Hide Columns where Pivot tables are available i.e. Hide Columns A to G, Hide Gridlines and Headings

 You can select any Slicer items and both the Charts will change accordingly; You can select multiple slicer items by using ctrl + click.

5. SharePoint 2010 Integration

Upload Excel file in SharePoint library and right click on it to select 'View in a browser' option. Slicers can be used through SharePoint as well :)