The problem is that if you have dates as the X-axis series for a chart, the
only options for Base Units are Days, Months or Years. If you have
quarterly data, excel adds extra space between the data points.
A simple example illustrates the problem:
Create a column chart from the following data
Note that the dates to appear on the X axis are the end of each quarter
(every 3rd month)
Create a simple Column chart from the data.
Right-click on the data series and choose Format Data Series, Options
Set the Overlap and Gap-width to 0.
You will see that there is still a large gap between each Column. Setting
both to 0 for a column chart normally expands the width of the columns so
that they fill the entire width of the chart.
In this case, each column only fills 1/3 of the width available.
To see the cause, right-click the X-axis and choose Scale. Note that the
only Base units are Day, Month and Year.
The reason for the extra space between the columns is that Excel is
space for the missing months even though there is no data for them.
Does anyone know a way around this?
The only way I have found is to convert the dates to text, so that Excel
does not recognise them as dates.