Pivot Chart Bar Width

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to stabilize the width of the bars when using a Pivot Chart?

When there is very little data to chart - based on the values I select from page data pull-downs - the bars are really fat. Other times they are thin. I would really like to have a standard width.

tia
Pat Watson
 
Pat -

The bars are based on a percentage of the spacing between categories.
If you have fewer categories, the bars get wider. If you decreased the
bar width (by increasing gap width on the Other tab of the format series
dialog), they'd be much further apart.

You can't adjust these automatically, but you can run a simple macro
every time you adjust the chart. This one assumes a clustered column
chart type:

Sub FixClustColWidth()
Dim Npts As Integer
Dim Nsrs As Integer
With ActiveChart
Npts = .SeriesCollection(1).Points.Count
Nsrs = .SeriesCollection.Count
.ChartGroups(1).GapWidth = WorksheetFunction. _
Max(0, 3000 / Npts - Nsrs * 100)
End With
End Sub

If it's a clustered bar chart (horizontal) you might change the factor
in the last line in the With construct from 3000 to 2000. If you have a
stacked column or bar type, let Nsrs = 1.

- Jon
 
Back
Top