Pivot Tables

Question

Hi, how do i stop my pivot table counting the 0's and N/A's

So far I've tried the following If statements in my raw data which leaves the field blank, but the pivot is still counting the blanks ie =IF(Test!A2="","",Test!A2)

N/A but the pivot still counts those =IF(Test!A2="",NA(),Test!A2)

Any ideas?

Answer 1

Dear @Suki Sangha,

From your description, I understand that your PivotTable is still counting values that you expected to be excluded, including 0, #N/A, and cells that look blank because they are returned by formulas such as =IF(Test!A2="","",Test!A2). You also mentioned that using NA() did not stop the PivotTable from counting those entries.

A possible reason is that a PivotTable using Count will count any value that is not truly empty. A formula returning "" only looks blank, but it is not treated as a truly empty cell by Excel. In the same way, #N/A is still an error value, and 0 is still a valid number, so they can still be counted depending on the summary setting being used.

As an initial step, if your field is numeric, please try changing the PivotTable summary from Count to Count Numbers:

• Right-click the value field in the PivotTable.
• Select Summarize Values By.
• Choose Count Numbers.
• This will ignore text, blanks, and errors such as #N/A, but it will still count 0 because zero is a number.

For example:

If you also need to exclude 0, the better option is to use a helper column and then summarize that helper field by Count  Number. For example: =IF(OR(A2=0,ISNA(A2),A2=""),"",1)

Then add this helper column to the PivotTable Values area and set it to Count Number. This approach counts only the valid rows that return 1

Please understand that the initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Your detailed response will help us diagnose and investigate the issue more efficiently. If I misunderstood what you’re looking for, feel free to let me know or share a screenshot. I’d be happy to help further!

Thank you for your cooperation. I'm looking forward for your reply.

---

If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Configure the PivotTable so that zeros and N/A (errors) are not counted or displayed:

1. In the PivotTable, click any cell.
2. Go to PivotTable Analyze (or Analyze / PivotTable tab depending on version), then click Options.
3. On the Display (or Layout & Format) tab:
   • Under Format, check For error values show and leave the box empty to show errors as blanks instead of #N/A.
   • Under Format, check For empty cells show and delete any characters in the box so empty cells display as blanks.

This makes #N/A and empty cells appear as blanks, so they are not counted as values in the PivotTable.

If the source formula is returning 0 and those are being summed or counted, change the formula to return a blank instead of 0, for example:

• =IF(Test!A2="","",Test!A2)

Then ensure the PivotTable is refreshed after changing the formulas.

---

References:

• Hide error values and error indicators
• Display or hide zero values
• Hide error values and error indicators in cells