Thread: Adding missing months to a chart

The company I work for has 3 divisions and I collect the same data for each into one big spreadsheet.

Every month I have to produce a column chart for each division showing the same data trend over the last 12 months however the way I do this is rather convoluted and I am sure it could be streamlined.

My problem is that the data I show in the column chart may not necessarily accumulate every month i.e. there may be a month where nothing happens. How do I create a chart that takes these missing months into account? That inserts those missing months in the horizontal axis while the vertical axis marks the Absolute value I am tracking (ABS)? The example below shows a simplified version of how the data is arranged.

I have considered inserting some bogus months but that seems almost as awkward a workaround as the current method I use which involves copying and pasting into 3 other worksheets.

Division Month ABS
A 01/2008 £7,787
A 02/2008 £2,394
B 02/2008 £8,606
A 04/2008 £6,886
C 05/2008 £10,071
C 04/2008 £2,311
A 07/2008 £481
A 09/2008 £888
B 10/2008 £1,039
C 05/2008 £10,071
C 04/2008 £2,311
A 07/2008 £481
A 09/2008 £888

One would think you need a simple "summary" table that draws over the totals for each month. In your summary, you already have all the months represented, and the SUMIF formulas that bring over the pertinent value would find no data for the empty months so those months remain empty for SUMs.

But then your "chart" is created from your summary table, not from the raw data.

Attached is a simple workbook example from your data showing what I mean.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]