Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I created a Pivot table and have both negative and positive values in my Grand Total column.

enter image description here

My intention is to show only positive Grand Total values.

Thank you to all for your guidance.

EDIT by Doug: Here's the image of the source data:

enter image description here

share|improve this question

2 Answers 2

Select the Grand Total column, and in the Format Cells>Number dialog, choose Custom. In the format box enter 0;;.

EDIT based on comments:

Assuming that your source is in an Excel 2010 table, which looks like it might be true from your image, here's a formula that you can in include in a new column in the source table:

=SUMPRODUCT(([Age]=[@Age])*([Date_Last_Pay]=[@[Date_Last_Pay]])*([Acc_No]=[@[Acc_No]])*[Last_Amount_Pay])>=0

The formula sums all other Last_Amount_Pays that have the same date, age and account number. The formula results in TRUE if that amount is equal to or greater than 0.

You can then use that column as the Page Filter for your pivot table and filter it to TRUE.

Let me know if this works for you. If not, please tell me what version of Excel, and which columns the conatain the headings listed in this formula.

share|improve this answer
    
Of course if you're showing a Grand Total at the bottom, this won't work because the row totals will add up to something else. –  Doug Glancy Aug 21 '12 at 21:57
    
Thank you Doug. The solution provided works except that I am thinking of not showing the entire row that has negative grand total. This is possible? –  Chris Aug 22 '12 at 0:24
1  
The best option with things like this is to add a helper column to the source data that calculates whether each row should be included in the pivot. You can the filter the entire pivot by that column/field. In this case you need some kind of SUMPRODUCT formula that determines whether any given row contributes to a negative grand total row in the pivot. If I saw your source data I could probably figure it out. If your needs are strictly visual, you can filter the pivot table by selecting a range outside of it to filter. That's not very maintainable though. –  Doug Glancy Aug 22 '12 at 3:37
    
Hi! Doug, I will make out a sample based on my actual data. Can you advise me as to how I can send the file to you. Thanks –  Chris Aug 22 '12 at 3:51
    
If you just put an image in the question, that should be fine. I just need to see which columns things are in. I hope :). –  Doug Glancy Aug 22 '12 at 14:08

Use can use a Value Filter for this purpose. Assign a value filter on a Row Label column, usually one that contains one distinct value per row, such as ACC_NO in your example.

Here's a simple example that will hide all negative totals (the 3rd row). I picked the Quarter column to hold the filter (more on this below).

enter image description here

To enter a Value Filter, simply go in the filter drop-down on the column you want to apply your filter on:

enter image description here

How to choose which column to apply a total filter on?

Note that I chose to do the filter on QUARTER instead of ITEM because the elements of this column are not grouped. Therefore, in this case, the filter will be applied to each individual row.

If instead I did the same value filter on the Item column (where we can see Item B is grouped), then the filter would apply on the subtotal for each group. For example, since the sum of Q1 and Q2 for Item B is negative (-5), both Item B rows would be filtered out.

In your sample, all your rows are unique because of the leftmost ACC_NO, so you would get the same result by placing the filter in any column.

Final thoughts:

  • If you have multiple totals in your Values area (eg. a count and a sum), you can pick which one to filter against in the dialog.
  • If you have a Column Label to split your totals, the Value Filter will be applied against the grand total only.
  • Applying a Value Filter on a Column Label instead of a Row Label will perform the filter against the vertical totals at the bottom.
share|improve this answer
    
That is so great! I never knew you could do that. It's not very intuitive, but I'll remember it. Is this avalaible in XL 2003 or just later versions? –  Doug Glancy Aug 23 '12 at 3:39
    
@Doug Glancy It's there in 2007 and up, not sure aout 2003. I agree it's probably the least intuitive filter. –  mtone Aug 23 '12 at 3:42
    
I just took a quick look at 2003 and it doesn't seem to be part of it, which I'd expect, as both filters and pivot tables were expanded in 2007. This is great, I really didn't like my convoluted answer. +1, and I hope @Chris is working with 2007 or 2010. –  Doug Glancy Aug 23 '12 at 3:53
    
Thank you guys for all your help! –  Chris Aug 29 '12 at 6:17
    
@Chris You're welcome! Please remember to upvote & mark as answered where applicable. –  mtone Aug 29 '12 at 8:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.