Drill to Detail With Excel Slicer Filters

Excel Slicer With Slicers in Excel 2010, you can easily filter several pivot tables with a single click. In the screen shot below, the Slicers are filtering the Severity and Priority fields in the pivot table.

Excel Slicer Detail 1

Unexpected Results With Drill to Detail

Thanks to a tip from AlexJ, I learned that Slicers, combined with a pivot table’s Drill to Detail feature, can produce unexpected results.

Double-click on a pivot table’s data cell, and a new sheet is inserted in the workbook, with a list of the records that are included in the cell that you double-clicked. For example, in the screen shot above, there are 2 records in the detail sheet, if you double-click on cell G6.

ExcelSlicerDetail02

But what happens if you remove the Priority Report Filter from the pivot table, and filter with the Priority Slicer? The quantity in cell G6 is still 2.

ExcelSlicerDetail03

But, when you double-click on cell G6 now, with the Priority field removed from the layout, the detail sheet looks different. There are 8 records, instead of 2 – all of the Priority values are included, not just the “20” priorities.

ExcelSlicerDetail04

Quirk or Feature?

Is this a quirk or a feature? I’m voting that it’s a quirk. When I double-click, only the records behind the visible number should be displayed in the detail list.

Keep this quirk in mind, if you’re using Excel Slicers to filter the data, and then you use the Drill to Details (Show Details) feature. Only the fields that are in the pivot table layout will be filtered in the detail results sheet.

If you’re using a macro to automate the drill to details feature, you might not notice that the results include non-filtered items. To prevent errors, you could add all the slicer fields to the pivot table layout, and that will ensure that the details list includes only the expected records.

Download the Sample File

To test the effect of Slicer filters on the pivot table drill to detail feature, you can download Alex’s sample file, PT0026 – Pivot Table Slicer Detail, from the Contextures website. NOTE: Slicers are only available in Excel 2010, so you won’t see them in earlier versions.

Watch the Drill to Detail Video

To see the steps for connecting multiple pivot tables to a slicer, then drilling to the details, please watch the short tutorial video below.

Or watch on YouTube: Drill to Pivot Table Details When Using Excel Slicers

_______________________

You may also like...

13 Responses

  1. Ken Puls says:

    That looks like a bug to me, Deb. I think you should report it to Microsoft.

  2. Brent says:

    Glad I found this. I thought I was crazy. Have you heard anything further about this issue?

  3. Mike C. says:

    Any update on this from MS? I haven’t seen anything posted, and even tried to look up the issue on TechNet and couldn’t find any resolutions.

  4. Refa says:

    Hi Debra.

    I am grateful for this possibility to Drill in the source table when Iam using powerpivot, where the sources are from SQL Server.

    But my problem is, i get only the first 1000 rows (EXCEL 2013).

    Can you give me please a tip, how i can retrieve all my rows during pivot table’s Drill to Detail feature?

    Thank you in advance.

  5. Florian F says:

    Thanks for that, Debra. I had the same problem and did not know why the drilling was displaying all the data. I suspected to be an error somewhere in the setting up of the slicers. At the end, I solved it by adding same page fields corresponding to the slicers and then hiding them with a colored shape.

  6. Sam says:

    Hi Debra,

    Thanks! Excellent video you have made :) It seems this problem also exists in Excel 2013.

    /Sam

  7. AcL says:

    Fixed in 2016 – all kudos to following

    http://datapigtechnologies.com/blog/index.php/be-careful-drilling-into-pivottables-with-slicers/

    10.Carl Farrington

    April 22, 2016 at 5:40 pm

    FYI, I just tested this and it is fixed in Excel 2016.
    I have a 365 subscription with the choice of either 2013 or 2016 apps, and although both suites were up to date (subscription version auto updates), the problem was present in Excel 2013 but not in 2016. You would think they would fix this in a service pack for 2013. We can’t move the rest of the office to 2016 because we’re running with Exchange 2007 which won’t work with Outlook 2016.

  8. Tomer says:

    Guys, I have found a solution for this. Just add the filters inside the pivot , AS WELL AS keeping the slicers. This way it will work just fine :)

  9. Dave says:

    I’m still getting “unexpected” results when setting slicers and then drilling down in Excel 2016 using PowerPivot. It seems if I have multiple items selected from a single slicer it is more likely to return an incorrect results set.

  1. July 2, 2012

    […] When I got back, a quick Google search confirmed that Debra Dalgleish of Contextures fame beat me to it and found this “quirk” earlier this year. […]

Leave a Reply to Tomer Cancel reply

Your email address will not be published. Required fields are marked *