+ Reply to Thread
Results 1 to 2 of 2

Calculating with N/A in pivot table source

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    Calculating with N/A in pivot table source

    Hello you knowledgeable people, I hope someone can help me or at least tell me if it's not possible.

    I have several pivot tables that generate from a very large sheet of values broken down into sections. The values for these are either numeric or N/A and I have the pivots calculate average values from the data.

    My problem is that some of my sections are completely valued with N/A and when the pivot result is calculated it shows 0%. What I am trying to get is this value to be 100% as it used in performance metrics, or even better for the pivot to show N/A.

    Is there a way to do this ?

  2. #2
    Registered User
    Join Date
    Guwahati, Assam, India
    MS-Off Ver

    Re: Calculating with N/A in pivot table source

    If you want N/A to be shown as 100% in your Pivot Table then you may follow the below Steps:

    Insert PivotTable.
    Check all the PivotTable Fields.
    Change the PivotTable to the classic layout (or keep the standard one if you prefer).
    Click inside the PivotTable and Press Alt + A to select all PivotTable data.
    Choose Home >> Styles >> Conditional Formatting >> New Rule.
    Choose Format only cells that contain.
    In Format only cells with, choose Cell Value, and equal to, then type N/A
    Click the Formatů
    Choose the Number tab and Custom
    Inside the Type field, insert 100%
    Click OK.
    This rule replaced all strings consisting (N/A) to 100%
    If you add missing values to the table, nothing will change inside the PivotTable. If you want to apply changes, you have to click it with the right mouse button and choose Refresh.
    Asha Kanta Sharma
    Guwahati, India

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1