Pivot table - see the filters that calculated cell contents

Hello

I'm creating some quite complex pivot tables that will be used in Compact view by people without much pivot table experience.

I would be useful if they could easily see the calculations that the data in each cell derives from.

E.g. On one there are two row labels, the first filtered to all but blanks, the second to two specific values. The column labels are further filtered to remove blanks, and the values field is a calculated field pulling totals from several source columns.

But I'm not sure it's that obvious to someone with only a bit of pivot table knowledge!

Is there any quick and easy way for users to say hover over a cell and see something like:

Sum of (widgets AND thingies AND gizmos) where salesmanager=(sid OR jane) and customer=(whatever Row Label value we're over)

I'm not talking about the underlying source data, just the calculations.

Thanks

Thanks both - good advice, and yes, I've named the calculated field and the row labels.

But I'd like users to be able to see past my name for the calculated field so they can validate that they understand and agree that it's the correct data being calculated.

For the row labels, having a fixed label is a start, but if someone else changes the filter and saves then it might make things even more confusing. Even for experienced pivot table users it'd be helpful to have immediate feedback on what filters are in force - for instance if there are 3 row fields it's a pain to have to check each one from the dropdown and easy to miss all the ticks

Forgot to say: using Excel 2007 by the way.

Thanks

Thanks DonkeyOte

I've added a second table and attached it below to give you an example.

Hover your mouse over j16.

There are 5 rows with an 'a' and a 'p' but only one is included in the calculation. As far as I know the only way to identify why is to:

1. Go to the row labels filter
2. Check the filter applied to f1
3. Choose the dropdown next to f1
4. Check the filter applied to f2
5. Choose the dropdown next to f2
6. Check the filter applied to f3
7. Cancel out of the filter

If you aren't used to using pivot tables a fair bit, that's by no means obvious.

And even if you are used to using pivot tables, I challenge you to do that and remember what the criteria in f2 was after you close the filter panel

Obviously with p's and q's and whatever it's a bit artificial, but this kind of structure is pretty typical of the sorts of tables I'd be using.

Does that clarify?

Thanks

Thanks all

MarvinP: yes, that's the sort of thing I'm after. Now I just need a bit of data that somehow fools my boss into paying for us all to upgrade to Office 2010

DonkeyOte - thanks for the code - haven't had a chance to look at it yet but I promise some feedback as soon as I can.

Update - had a look at the code - absolutely what I had in mind and behaves just the way I'd expect Excel to work out of the box.

I'm not too strong on VBA but I'll definitely spend some time working through it to see if I can incorporate it into my production workbooks.

Thank you so much