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

Quarter Value
31-Mar-04 3
30-Jun-04 6
30-Sep-04 9
31-Dec-04 12

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 leaving
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.

thanks

Peter

Hi Peter,

The only way to get rid of the gaps is to make the axis a category axis.
You have already discovered that by changing the dates to text the axis
becomes a category one.
Another alternative is to specify that the axis should be category.
To do this right click the chart and pick Chart options from the popup
menu. On the axis tab select category.

Cheers
Andy


Peter Carr wrote:
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

Quarter Value
31-Mar-04 3
30-Jun-04 6
30-Sep-04 9
31-Dec-04 12

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 leaving
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.

thanks

Peter


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info