Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 15th 04, 07:03 AM
Peter Carr
 
Posts: n/a
Default Problem with date base units for x axis

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


  #2   Report Post  
Old December 15th 04, 10:11 AM
Andy Pope
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Labels on Chart with Negative Value Axis David F. Schrader Charts and Charting in Excel 6 December 17th 04 07:25 PM
Y axis, need varying units NTE Charts and Charting in Excel 10 December 13th 04 07:34 PM


All times are GMT +1. The time now is 01:49 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017