Insert zero count values into axis on Excel Chart

[idbr]

Hi,

I'd like to create a chart that has placeholders on the axis for zero value categories. Probably best if I give an example:

Let's say I have this pivot table, based on a column with values in it that I'm counting:

Count of X-Value	
X-Value	Total
1	9
2	16
3	18
5	25
6	29
7	1
10	2

I want to create a column chart that has a 'column' for each integer between the minimum and maximum values (obviously where these don't exist I wouldn't have an actual column, just a space). So, my x axis would look something like this:

-----------------------------
1  2  3  4  5  6  7  8  9  10

However the chart I've got looks like this:

--------------------
1  2  3  5  6  7  10

min(X-Value)/max(X-Value) can change but are always integers. I can probably code a solution if that's what's required, am hoping there might be a simpler way tho...

Any help would be appreciated.

I run XL2007 on XP

Thanks, Iain

 

[SkipVought]

Hi,

If you use a PT, you're struck with what you have.

I do this quite often where my x-axis is Date/Time but I don;t want to to specify the axis as time for formatting purposes.

So I start with a list that represents my x-axis values, in your cases 1 to 10. Then in the Y-axis column, I CALCULATE the value from my data as a conditional SUM, COUNT or a LOOKUP.

Skip,

_[red][/red]