Filter by calculated field

[Zyphon]

Hi Guys,

I have a spreadsheet exacted by Business Objects for our staff database and I wanted to make a pivot table to clearly show which members of staff have oustanding amounts on their records.

What I have to two fields one with Credits and one with Debits. I made a calculated field in my Pivot Table to to Add the Debits to the Credits so basically the formula is: Credits+Debits.

This works fine and shows a total of 0 for those who do not have an outstanding balance. However I know 3 of our staff do not have a 0 balance.

The trouble is there is 6000 lines of data to go through and I wanted to know how could I filter my Pivot Table to only show those employees who do not have a zero balance.

I have uploaded a sample of my spreadsheet, please could someone look at it for me and explain what it is I need to do to filter as Excel will not let me use a calculated field in the Row Data.

Thanks in advance.

[Zyphon]

Message to Moderators: I have put this in the Programming section instead of the Excel Miscellaneous section.

Would someone kindly move it for me please.

Sorry for this mix up.

[ncb]

Hi,

You could use an extra filter, just left of the pivot table, not in the pivot table.
In the filter you can can choose for none zero values.

Greetings

NCB

[Zyphon]

Hi,

You could use an extra filter, just left of the pivot table, not in the pivot table.
In the filter you can can choose for none zero values.

Greetings

NCB

Thanks for your help but to be honest I don't quite understand what you mean. I tried making a column to the left and adding a filter field but what am I filtering?

I need to filter the totals in this Pivot Table so that only the totals that are not 0 can be seen.

Thanks.

EDIT: Sorry yes I see what you meant the AutoFiltering has carried on to the other fields. This works but I only want to Filter the totals that are not zero and this method pulls out all of the other data that isn't 0 also. Any ideas?

Thanks in advance.