How to deal with blank cells for Pivot Charts

[DKDiveDude]

I have blank cells in my Excel 2003 pivot chart data table. When I switch from displaying one variable to another on the chart, Excel automatically defaults to COUNT the cells instead showing the SUM of the cells. How can one correct for this, other than putting a zero in each blank cell in the data table? Is there perhaps some non-numeric value that can correct for this?

 

[ETID]

Have you tried; right click in pivot table then "table options" and put a zero in the "for empty cells show"
option.

 

[DKDiveDude]

Thanks for the reply ETID,

but I have data where I do not know yet what the data is, and cannot not just slap zeroes all over the table(s) causing the chart to treat the cells as such.

Keep in mind that I only have this problem when I pivot my data from one set to another, AND where the other set has blank cells, causing Excel to get confused and defaults to COUNT instead of how the other data set was displayed, example SUM.

This pivot switching hickup do not occur when there is values in all cells in the table source, but as I said putting a zero in is not how I want it displayed, I want the blanks to be skipped.

 

[ETID]

I used vba in the Worksheet_PivotTableUpdate function
my Summed field source named is REFRNUMB
and the pivot tag for the sums is renamed to REFRNUMB_data



Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim i As Integer
With ActiveSheet.PivotTables("PivotTable1")
For i = 1 To .PivotFields.Count
If .PivotFields(i).Name = "REFRNUMB" Then 'check to see if this is the data field
If ActiveSheet.PivotTables("PivotTable1").PivotFields("REFRNUMB_data").Function <> xlSum Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("REFRNUMB_data").Function = xlSum
Else
End If
Else
End If
Next i
End With

 

[DKDiveDude]

Thanks again ETID,

and I might try your solution, even though it looks like you have two "else" not needed in your code, actually one that is unneccessary and one that is orphaned.

I am still searching for a simpler solution, non-programmatic, which can be presented and used easy by non-programming Excel users.

 

[ETID]

It worked on my machine...two conditions based one based on the other.


It could be written a few different ways...


even in a select case conditional.

 

[ETID]

Oh ...the pivot refresh/calculate event will launch the code with out user interaction.

Another way is to add a column in the source date with an =if(cell_ref="",0,cell_ref*1) then use that field in the pivot. (or does that pose the same problem with zero's)?

...unless of course you're linking the pivot directly via sql

 

[DKDiveDude]

Here's what I mean the code should look, withou the two unneccessary "else"

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim i As Integer
With ActiveSheet.PivotTables("PivotTable1")
For i = 1 To .PivotFields.Count
If .PivotFields(i).Name = "REFRNUMB" Then 'check to see if this is the data field
If ActiveSheet.PivotTables("PivotTable1").PivotFields("REFRNUMB_data").Function <> xlSum Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("REFRNUMB_data").Function = xlSum
End If
End If
Next i
End With


You had nothing in the "else" nest, making them unneccessary.

 

[ETID]

true...