Slicer Selection Does Not Allow Filtering of Results in the Pivot Table When the Cell is Double-Clicked

[Edward Watson]

Using Excel 2010, I can double click any cell containing data in pivot tables and get all the entries for that particular cell onto a new sheet. For example, if I want to know where the $100 total in a cell comes from, I just double click on the cell and a new worksheet pops up listing all the entries that added up to $100 for that cell's category.

So far so good.

However, when I create a slicer for that pivot table, and use it to filter it, let's say according to salesperson, if I were to do the same thing to see that salesperson's results for the same cell (let's say, it now says $50), the sheet that pops up does NOT give me a listing of just the entries that added up to $50. It gives me the SAME number of entries that exist for that cell as if the slicer selection does not exist despite the pivot table shows the correct totals from the slicer.

I took some screenshots to explain my problem but this interface apparently will only allow pictures from a URL, so I can't upload them.

HELP!

 

[Worf]

this interface apparently will only allow pictures from a URL

True. Are you facing a workplace firewall? Can’t you upload at home?

 

[Edward Watson]

I'm puzzled as to why the feature to upload is only for a URL. I'm assuming if I use a cloud site like Dropbox that I can insert the jpegs of the screenshots.

I'll do that when I get home from work.

Here's more details of my problem:

When I build a simple pivot table from my data set, I can double click into a cell containing data to retrieve all the entries that make up the total for that data cell. For example, if I'm tracking product sales, I'm collecting data for product, brand, sales person, sales area, as well as the sales. So, five pieces of data for each entry.

I built a pivot table using product as the row label, brand as the column label, and sales amount sum as the value. My pivot table then tells me my company's sold $284.18 worth of bubble gum, and when I double click on that cell, a spreadsheet opens up with four records telling me that this amount was shared between three salespersons and the sales from each person.

Now, when I use the sales area slicer and select one area out of five total, the pivot table values change (naturally). What was originally $284.14 now reads $113.98 because the latter amount was sold within that specific sales area. So far so good.

However, when I double click the cell that now reads $113.98, the spreadsheet that opens up still has the original four records totalling $284.14. It still assumes the slicer didn't narrow the search conditions despite the pivot table already changed to the new values because of the slicer selection. How can I make the pivot table just pull up the records based on the slicer selection when the cell is double clicked?

I hope this detail is clear enough.

 

[Edward Watson]

I created a pivot table with several slicers. When I double click on a cell to see why we had monthly sales of Brand A ice cream of -$166.52 (see image No. 1).

A temporary sheet pops up listing all the records associated with that cell. In it, I see that the negative sales was due to one salesperson, M. Mouse, who apparently had to refund a major client (see image No. 2).

However, when I use one of the slicers to just examine the performance of one of the salespersons and double click on the same cell (see image No. 3), it pulls the exact same worksheet with the same records as No. 2 image instead of just giving me the record that is filtered according to the slicer.

How can I make the pop-up worksheet just show the records in accordance with the slicer filter?

Thanks!

 

[Edward Watson]

Ok, why can't I see the images that I linked from Dropbox?

 

[Worf]

1) I cannot see the images either. Did you put them at drop box’s public folder?
2) I understood the problem and will work on it. My idea is that if we cannot get the double clicking working, use VBA to produce the desired table.

 

[Worf]

The solution: Be Careful Drilling into PivotTables with Slicers – Bacon Bits

 

[Edward Watson]

Thank you very much. That seems to be the solution except I'm having a hard time picturing what the solution's supposed to look like.

What does "The only fix seems to be to also include the Report Filter in the PivotTable along with the Slicer for the target field. This will negate the issue. Of course, you can hide the row that contains your report filter so your clients won’t get confused."

So, HOW do I include a "report filter in the pivot table along with the slicer for the target field"?

I'm glad to know my Excel 2016 doesn't have this problem but I'm restricted to Excel 2010 at work.

 

[Edward Watson]

Halleluyah! success! Thank you! I thought about what the solution was and figured out what I was doing wrong.

Thank you for your help!

Ed