Keep complete date series on X axis

[Jeff Eckermann]

I have data grouped by month, as follows:
Customer Month1 Data1 Data2 ....
Customer Month2 Data1 Data2....
....... ...... ..... ......

I am creating a pivottable & chart to show customer data series, with the
months on the X axis. Works well, but when I am displaying a series that
does not have data for all months, the absent months are left off the X axis
(logical enough: how should Excel chart data that doesn't exist?). What I
want is for the X axis to always show all months, regardless of the data
i.e. there should be gaps in the data for missing months, as there is when
multiple series are shown. Relevent point: I am running all of this from
VBA code, including the copying in of a recordset from an Access database.

This doesn't seem like it should be such a hard thing to do, but I have
researched this every which way, and can't find a good way to do it. The
only solutions I have thought of are:
1. Loop through my recordset and add customer & month entries (with missing
data) for all missing months (Ugh).
2. Some kind of voodoo with a dummy series (haven't figured this one out
fully though).

I would be really, really grateful for any pointers.
TIA

 

[Kelly O'Day]

Jeff:

It sounds like you are using a line chart, not an XY chart.

Line charts treat the X axis data as categories, even if the data numeric.

XY charts threat the X axis data as numbers and plot them based on numeric
value.

Check your chart type.

For trend charts and chart types, you may want to look at this page.

http://processtrends.com/pg_charts_trend_chart.htm

(e-mail address removed)