Pivot table - show detail for only negative grand totals

Once option is to actually use auto filter. Highlight the row with your column headings in the pivot table plus one column to the right (you must start at the right or auto filter will not be an option).

Example: If you pivot table is in columns A - D then highlight column E - A and select auto filter. You will then have the ability to filter for all rows in your grand total column that is less than 0.

Thanks - this is a helpful hint for the sorting.

I don't think it gets me any closer on easily showing the detail for all the negative values.

Any thoughts?

Hi, Jeff.

The question would be easier to answer with a more definitive description, such as would be available with some sample data.

As I understand, the requirement is to show only data where the sub-totals are <0.

I guess this is for just one particular field? Or is this sort of functionality required for which field the user chooses. I'll assume for just one fixed/known field. I'll call that field "Account" for the purpose of description, and the field being summed "Cost".

One approach might be to have a second pivot table giving the sub-total by Account. And then add one new field, say "ShowIt" to the source data with a simple GETPIVOTDATA<0 to return either TRUE for sub-totals < 0 and false for others. Then in the original pivot table, make this new field a page field and set it to show the TRUE data. Now to get the result, first refresh the new pivot table and then the original pivot table. So, two pivot table refreshes each time. There will be a bit of memory required for this.

A neater way, without an extra pivot table and without adding an extra field to the data is via SQL. Such as, in a new workbook start the pivot table wizard and at the first step take the external data option. Proceed and connect to the source data (easiest if you give the source data a normal [not dynamic] named range before starting) and continue into MS Query. Then hit the SQL button and edit the SQL to be like below. Untested. Please modify to suit your table & field names,
Exit MS Query with the 'open door' icon and complete the pivot table. The completed pivot table can now be moved back into the source file if you wish. This single pivot table does the job and is refreshed in the normal manner.

HTH, Fazza