nulls in pivot table

[DSL1970]

Typically you cannot put text into Pivot tables, but does anyone have any idea on how I can change nulls into zero in a pivot (maybe in VBA)? Problem is I am doing a calculation from Pivot table results and fields that have null values puts my columns out of whack and throwing my calculations off.... Or anyone any suggestions....... I have copied data to get zero's, but it is subsets of the data that I need in the drop downs, so that doesn't work for me.
Thanks,

DSL1970

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Are your nulls in the Data area of the Pivot Table?
If so, what type of Summary is being done (Count, Sum, ...etc)?

 

[DSL1970]

Yes the nulls are in the data set, and in the pivot it is a count and percentage thereafter. The nulls come from folks not starting a project, then some are in progress and some have completed..... if a full group has not started, the In progress and Completed columns go away and the total column that I was using as a calculation loses its place so the calculation doesn't work out. 4 columns - not started, in progress, completed and total....

 

[Jerry Sullivan]

Have you tried....
PivotTable Options > Layout & Format > Format > For empty cells show: > enter 0

If that doesn't fix things, please clarify how you're getting the total column. Are you using the Pivot's Total, or have you made a Calculated Item?
If it's a Calculated Item, what formula are you using?

 

[DSL1970]

I will check the PivotTable Options - The total Column is a sum from the Pivot table, then I am doing a percentage of "In Progress" and "Completed" to the side...

So columns are set up as "Not started", "In progress", "Completed" and "Total". I am taking the "In progress"/"Total" (as percentage) and "Completed"/"Total" (as percentage) as columns next to Total, just if no-one is either "In progress" or "Completed""... or both, my calculation is thrown off due to those columns not showing up as nulll data. In my dataset, each person only has1 of the 3 statuses, so team C may not have have anyone that has even started, so when drilling down on this team, in progress and completed don't really have a reason to show....

 

[DSL1970]

Have you tried....
PivotTable Options > Layout & Format > Format > For empty cells show: > enter 0

If that doesn't fix things, please clarify how you're getting the total column. Are you using the Pivot's Total, or have you made a Calculated Item?
If it's a Calculated Item, what formula are you using?

I have top approach, but still no luck.

 

[Jerry Sullivan]

Ignore this. My reply was posted twice. See latest below.

 

[Jerry Sullivan]

I'm sorry, but it's still not clear to me whether the Total Calculations that are giving you problems are from Calculated Items (in which you enter a formula), or the PivotTable's SubTotal or Grand Total feature.

It would be a great help if you could either post a copy of your workbook (with any sensitive data removed) to a hosting site like Box.com or at least post a screen shot to make it easier to understand the organization of the PivotTable.