Controlling Gap Width and Series Separation in Column and Bar Charts in Excel 2007:-

Typically, the individual bars in a bar or column chart are separated by gaps. When scientists create histograms, they want to eliminate the gaps between bars.

Excel offers the GapWidth property, whose value can range from 0 to 500 to represent 0% to 500%. Figure (given below) shows a typical chart and a chart where the gap width has been reduced to 25%. Note that reducing the gap size automatically makes the columns thicker. To keep the columns narrow, you should reduce the width of the chart. The gap width setting applies to chart groups that contain bar or column markers. It can also be used to format the volume markers in volume-high-low-close charts or volumeopen-high-low-close charts.

(In the bottom chart, the gap width has been reduced from the default to 25%.)

The following macro changes the gap width to 25%:

Sub FormatGapWidth()

Dim cht As Chart

Set cht = ActiveChart

cht.ChartGroups(1).GapWidth = 25

cht.ChartGroups(1).VaryByCategories = True

End Sub

When you create a clustered column chart with two series, the columns for each data point touch, as shown in the top chart in the Figure (given below). You can use the Overlap property to cause the columns to overlap. Values from 1 to 100 cause the columns to overlap anywhere from 1% to 100%. Values from -1 to -100 cause separations between the data points. The middle chart in the Figure (given below) shows a 50% overlap. The bottom chart shows a –100% overlap.

[The middle chart has an overlap, and the bottom chart has a negative overlap (that is, a separation).]

The following macro creates a 25% overlap between the series:

Sub FormatOverLap()

Dim cht As Chart

Set cht = ActiveChart

cht.ChartGroups(1).Overlap = 25

End Sub

The Overlap property applies to clustered column and clustered bar charts. It should be set to 100% for the volume series in stock charts.