none
Dont display Pivot Table values less than 0

    질문

  • I have multiple records in my source data that add correctly when in the Pivot Table. But - I want the pivot table to only show a value if it is greater than 0 not less than 0.

    Source would show rows of:

    Col A

    1

    2

    -4

    Like i said the data in Col A calculates correct but the sum is -2. I don't want to display the - numbers in the table.

    2012년 6월 10일 일요일 오후 2:24

답변

  • Hi,

    I have an idea for this issue:

    1. Add a column into the table, suppose it is named as “sum_null”
    2. Each cell of this column has a formula, such as: =IF(SUM(B2:B4)<0,"null",B2)

             This formula means that if the sum of the data is less than 0, the cell will show “null”.

             3. Use a macro to clear the cells which the content is null.

    Here’s the code:

    Sub clear_null()

    Dim row_count As Integer

    Dim loop_num As Integer

    row_count = ActiveSheet.UsedRange.Count

    For loop_num = 2 To row_count

        If Cells(loop_num, 3) = "null" Then

            Cells(loop_num, 3).Clear

        End If

    Next

    End Sub

             4. Run the clear_null macro

             5. Insert the Pivot Table

             6.  Choose the “Sum of sum_null” field, right click-> PivotTable Options…->Layout&Format ->check “For Empty cells show:” then set the empty cells show “”.


    Jaynet Zhang

    TechNet Community Support

    2012년 6월 11일 월요일 오후 3:00

모든 응답

  • Hi,

    I have an idea for this issue:

    1. Add a column into the table, suppose it is named as “sum_null”
    2. Each cell of this column has a formula, such as: =IF(SUM(B2:B4)<0,"null",B2)

             This formula means that if the sum of the data is less than 0, the cell will show “null”.

             3. Use a macro to clear the cells which the content is null.

    Here’s the code:

    Sub clear_null()

    Dim row_count As Integer

    Dim loop_num As Integer

    row_count = ActiveSheet.UsedRange.Count

    For loop_num = 2 To row_count

        If Cells(loop_num, 3) = "null" Then

            Cells(loop_num, 3).Clear

        End If

    Next

    End Sub

             4. Run the clear_null macro

             5. Insert the Pivot Table

             6.  Choose the “Sum of sum_null” field, right click-> PivotTable Options…->Layout&Format ->check “For Empty cells show:” then set the empty cells show “”.


    Jaynet Zhang

    TechNet Community Support

    2012년 6월 11일 월요일 오후 3:00
  • For that you should tell us how many field is in Value area and how many in Row/Column Area.
    2012년 6월 11일 월요일 오후 3:14