JohnKauffman
New Member
- Joined
- Nov 1, 2012
- Messages
- 36
I want to show from a set of values that for some values there is a high count and for others a count of zero (result in both tabular and chart form).
I make a pivot table with my range of values drug to both the Row Labels and the Values (as Count) and it works for for values where the count >1 but the values where the count = 0 there are no entries (rows) in the pivot table.
When converted to a pivot graph the graphical message is lost that some counts are zero because they have no blank/zero height columns. Only by inspecting the x axis labels can the user see that some x axis entries are missing.
I think somehow Excel thinks the x axis holds strings and so it does not arrange them as if along a number line which would include values with count=0.
How to get a Pivot table of counts to include an entry for every value, even if the count of the value is zero?
Example: Values 1-20 have a high count, 21-30 counts of zero, values of 31-50 high counts. I want a graph with x axis ticks at every value from 1 to 50. In the 1-20 range will be columns, no columns 21-20 and columns again in 31-50. That will clearly show there are zero counts for the middle values of 21-30.
I make a pivot table with my range of values drug to both the Row Labels and the Values (as Count) and it works for for values where the count >1 but the values where the count = 0 there are no entries (rows) in the pivot table.
When converted to a pivot graph the graphical message is lost that some counts are zero because they have no blank/zero height columns. Only by inspecting the x axis labels can the user see that some x axis entries are missing.
I think somehow Excel thinks the x axis holds strings and so it does not arrange them as if along a number line which would include values with count=0.
How to get a Pivot table of counts to include an entry for every value, even if the count of the value is zero?
Example: Values 1-20 have a high count, 21-30 counts of zero, values of 31-50 high counts. I want a graph with x axis ticks at every value from 1 to 50. In the 1-20 range will be columns, no columns 21-20 and columns again in 31-50. That will clearly show there are zero counts for the middle values of 21-30.
