Bar Chart Date XAxis Thin Bars

I have a bar chart with dates on the xaxis being resorted (primary axis
= automatic). There are 26 entries over a one year period. The trouble
is the bars come out as vertical lines, I can't set the xaxis format so
that they have any thickness. If I set the chart to not sort the xaxis
then the bars are okay.

Your axis is set so the base unit is days. Each bar must then fit within
a band 1/365th the width of the chart. If you change the base unit to
months, you will have two ro sometimes three points occurring at the
same month.

You shoucl consider changing from time scale to category for the axis.
Choose Chart Options from the Chart menu, click on the Axes tab, and
select Category under Primary X Axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

MrC wrote:

> I have a bar chart with dates on the xaxis being resorted (primary axis
> = automatic). There are 26 entries over a one year period. The trouble
> is the bars come out as vertical lines, I can't set the xaxis format so
> that they have any thickness. If I set the chart to not sort the xaxis
> then the bars are okay.

Thanks for the quick reply Jon.

WRT changing the base units to months, if I do this then I only get one
value for that month. The major and minor units have to be months as well.

WRT xaxis = category, if I choose this then the date order is gone.

Here is the matrix I'm charting on:
date series1
5-Apr-04 5
25-Jan-04 2
15-Mar-04 4
10-Mar-04 3
10-Jan-04 1

Thanks for your help....
BTW I'm using Excel 2000



Jon Peltier wrote:
> Your axis is set so the base unit is days. Each bar must then fit within
> a band 1/365th the width of the chart. If you change the base unit to
> months, you will have two ro sometimes three points occurring at the
> same month.
>
> You shoucl consider changing from time scale to category for the axis.
> Choose Chart Options from the Chart menu, click on the Axes tab, and
> select Category under Primary X Axis.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> MrC wrote:
>
>> I have a bar chart with dates on the xaxis being resorted (primary
>> axis = automatic). There are 26 entries over a one year period. The
>> trouble is the bars come out as vertical lines, I can't set the xaxis
>> format so that they have any thickness. If I set the chart to not sort
>> the xaxis then the bars are okay.

What do you expect XL to do? You want sufficient horizontal space for
365 entries and XL is giving it to you. The result is that each day
occupies only 1/365th of the horizontal space of the plot area.

You *might* get some additional thickness by double-clicking the
plotted series, selecting the Options tab, and setting the Gap Width to
zero.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, [email protected] says...
> Thanks for the quick reply Jon.
>
> WRT changing the base units to months, if I do this then I only get one
> value for that month. The major and minor units have to be months as well.
>
> WRT xaxis = category, if I choose this then the date order is gone.
>
> Here is the matrix I'm charting on:
> date series1
> 5-Apr-04 5
> 25-Jan-04 2
> 15-Mar-04 4
> 10-Mar-04 3
> 10-Jan-04 1
>
> Thanks for your help....
> BTW I'm using Excel 2000
>
>
>
> Jon Peltier wrote:
> > Your axis is set so the base unit is days. Each bar must then fit within
> > a band 1/365th the width of the chart. If you change the base unit to
> > months, you will have two ro sometimes three points occurring at the
> > same month.
> >
> > You shoucl consider changing from time scale to category for the axis.
> > Choose Chart Options from the Chart menu, click on the Axes tab, and
> > select Category under Primary X Axis.
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> >
> > MrC wrote:
> >
> >> I have a bar chart with dates on the xaxis being resorted (primary
> >> axis = automatic). There are 26 entries over a one year period. The
> >> trouble is the bars come out as vertical lines, I can't set the xaxis
> >> format so that they have any thickness. If I set the chart to not sort
> >> the xaxis then the bars are okay.
>

If you're saying that bar charts don't work in this case (ie the bars
are too thin to be of any use) then the software should prevent people
from specifying bar charts under these conditions.

Your "advice" to changing the gap is a non-starter....it doesn't work.
Its beginning to sound like this is a lost cause...

Tushar Mehta wrote:
> What do you expect XL to do? You want sufficient horizontal space for
> 365 entries and XL is giving it to you. The result is that each day
> occupies only 1/365th of the horizontal space of the plot area.
>
> You *might* get some additional thickness by double-clicking the
> plotted series, selecting the Options tab, and setting the Gap Width to
> zero.
>

Whether one likes a particular effect or not is a subjective matter.
To disallow a feature for its entire customer base because one person
doesn't like it would be a rather draconian step for MS to take,
wouldn't it?

You can use formatting to focus the eye on the bars. Double-click the
plotted series and from the Patterns tab set the border to none and
pick a bright color for the area (red or blue works well).

If you still don't like the effect, I don't know what to write. Just
keep this in mind. The bars are as thick as they possibly can be given
the requirement that there be space for 365 possible bars. To see
that, stretch the chart out horizontally over 20 or 30 columns. The
bars will get thicker.

If you feel strongly enough about this, let MS know. Visit
http://register.microsoft.com/mswish/suggestion.asp

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, [email protected] says...
> If you're saying that bar charts don't work in this case (ie the bars
> are too thin to be of any use) then the software should prevent people
> from specifying bar charts under these conditions.
>
> Your "advice" to changing the gap is a non-starter....it doesn't work.
> Its beginning to sound like this is a lost cause...
>
> Tushar Mehta wrote:
> > What do you expect XL to do? You want sufficient horizontal space for
> > 365 entries and XL is giving it to you. The result is that each day
> > occupies only 1/365th of the horizontal space of the plot area.
> >
> > You *might* get some additional thickness by double-clicking the
> > plotted series, selecting the Options tab, and setting the Gap Width to
> > zero.
> >
>

I have tried playing around with formatting the bars, colour, width,
gap, etc with no luck....no significant change. This feature works with
line and area charts but not bar charts.

Unless anyone has a way around this I would say the combination of time
scale and bar charts is not a valid combination so don't use it...even
though Excel supports it.

Tushar Mehta wrote:
> Whether one likes a particular effect or not is a subjective matter.
> To disallow a feature for its entire customer base because one person
> doesn't like it would be a rather draconian step for MS to take,
> wouldn't it?
>
> You can use formatting to focus the eye on the bars. Double-click the
> plotted series and from the Patterns tab set the border to none and
> pick a bright color for the area (red or blue works well).
>
> If you still don't like the effect, I don't know what to write. Just
> keep this in mind. The bars are as thick as they possibly can be given
> the requirement that there be space for 365 possible bars. To see
> that, stretch the chart out horizontally over 20 or 30 columns. The
> bars will get thicker.
>
> If you feel strongly enough about this, let MS know. Visit
> http://register.microsoft.com/mswish/suggestion.asp
>

If you leave the border on the columns, you will not likely see the
internal color. Tushar suggested hiding the border. If the width of each
bar changing from 2 pixels to 3 or 4 is insignificant, then I agree that
the change is rather insignificant.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


MrC wrote:
> I have tried playing around with formatting the bars, colour, width,
> gap, etc with no luck....no significant change. This feature works with
> line and area charts but not bar charts.
>
> Unless anyone has a way around this I would say the combination of time
> scale and bar charts is not a valid combination so don't use it...even
> though Excel supports it.
>
> Tushar Mehta wrote:
>
>> Whether one likes a particular effect or not is a subjective matter.
>> To disallow a feature for its entire customer base because one person
>> doesn't like it would be a rather draconian step for MS to take,
>> wouldn't it?
>>
>> You can use formatting to focus the eye on the bars. Double-click the
>> plotted series and from the Patterns tab set the border to none and
>> pick a bright color for the area (red or blue works well).
>>
>> If you still don't like the effect, I don't know what to write. Just
>> keep this in mind. The bars are as thick as they possibly can be
>> given the requirement that there be space for 365 possible bars. To
>> see that, stretch the chart out horizontally over 20 or 30 columns.
>> The bars will get thicker.
>>
>> If you feel strongly enough about this, let MS know. Visit
>> http://register.microsoft.com/mswish/suggestion.asp
>>

> WRT changing the base units to months, if I do this then I only get one
> value for that month. The major and minor units have to be months as well.

I was telling what would go wrong if you changed the base unit to
months. Not clearly enough.

> WRT xaxis = category, if I choose this then the date order is gone.
>
> Here is the matrix I'm charting on:
> date series1
> 5-Apr-04 5
> 25-Jan-04 2
> 15-Mar-04 4
> 10-Mar-04 3
> 10-Jan-04 1

If you want date order, but don't care about proportional spacing, then
sort the worksheet by date, then use the category axis type.


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______