Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Suppress 0's in a pivot table (Excel 2000)

    I have a pivot table and I want to suppress zeros if the amount is 0. I tried the Pivot table formula =if(Amount =0, "", Amount) and I get the value error. Can you help.

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    Just in case: would going to Tools|Options|View and unchecking "Zero values" (in the bottom middle) be of any help to your problem?
    Gre

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    I misunderstood the question. The question is, if the Amounts are all zero, then suppress the whole line. I have the items listed in Column A. Column B through M is the months and the Amounts are listed below. If the Amounts for an item are zero, then suppress the entire line in the pivot table. Thank you.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    If you have the grand total in the pivot, you could autofilter on this column and have it <>0 or >0.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    I don't understand your answer. The Grand Total column and row are part of the pivot table, but I don't understand how to auto filter on the pivot table. Should I auto filter before creating the pivot table?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    Go to the cell immediately to the right of the pivot table "header row".
    data-filter - autofilter and it will add it to the pivot columns

    Filter on the totals columns to eliminate the ones which total zero.

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    See if the macro in this newsgroup thread helps.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    that's it!!!!!!!!!!!!!!!!!!!!!!!!!!! After the auto filter, I choose the nonblanks. Excellent.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    Hans this is very close to what I need . My requirements just changed for a new pivot table The grand total column is not used in the new new pivot table. In this assignment, if the last two columns of the pivot table are zero, then the row needs to be suppressed. How can I change this macro to suppress rows. Thank you.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    Try this:

    Sub HideRowsIfLast2ColumnsAreZero()
    'variation on a macro by John Green
    Dim rRow As Range
    Dim lCols As Long

    lCols = ActiveSheet.PivotTables(1).DataBodyRange.Columns.C ount
    For Each rRow In ActiveSheet.PivotTables(1).DataBodyRange.Rows
    rRow.EntireRow.Hidden = _
    (rRow.Cells(lCols - 1) = 0 And rRow.Cells(lCols) = 0)
    Next rRow
    End Sub

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress 0's in a pivot table (Excel 2000)

    Thank you, I will try this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •