Show gridlines on top of series

[StuLux]

Is there any way to show chart gridlines on top of a series in a column chart.

I want to visually simplify my chart and show a white gridline on top of a dark coloured series and not show the gridline in between the data and anywhere else - in effect the column would have a stripe at every gridline interval. I'm using Excel 2007.

 

[VoG]

Maybe you could adopt this http://peltiertech.com/Excel/Charts/ArbitraryGridlines.html and use white gridlines.

 

[StuLux]

I had been looking at Jon's site and considered this as a method but thought that there may be a simple way to do this that I was missing in the options somewhere.

If I adopted Jon's method for putting horizontal lines on a chart I'm not sure how it would cope with varying scales.

 

[pgc01]

Hi Stuart

There'a a way to do it but is a bit twisted.

It's very easy. Copy the chart and in the new chart set for the

"Chart Area", "Plot Area" and "Data Series" the properties

Fill: None
Line Color: None

Also in this new chart, set the gridlines colour to white.

Now superpose this new chart on the other. You get The effect you wanted.

Notice the the 2 charts are the same apart from different colours or invisible objects. This means that you have no problem for example with scale changing.


Remark:

Although you can do this in 1 minute, I've already done this and the last part is difficult, to place the new chart exactly on top of the other.

For that I used vba. I didn't write a macro, simply executed the statements in the Immediate Window:

activesheet.chartobjects("Chart 2").top=activesheet.chartobjects("Chart 1").top
activesheet.chartobjects("Chart 2").left=activesheet.chartobjects("Chart 1").left

Adjust for the name of your chart objects.

As I said, this is a bit twisted, but it works and it's very easy to set up. I'll let you decide.

 

[StuLux]

Thanks PGC - that works for me but I really would have thought there would have been an option to put gridlines on top of chart series as this can make a bar chart easier to read and less cluttered.

 

[pgc01]

I'm glad it worked for you

This code should do it automatically for a column chart (or a bar chart). Just select the chart and run:

Sub WhiteGridlines()
Dim chtO1 As ChartObject, chtO2 As ChartObject
Dim ser As Series
 
' Create and position the new chart
Set chtO1 = ActiveChart.Parent
Set chtO2 = chtO1.Parent.ChartObjects(chtO1.ShapeRange.Duplicate.Name)
chtO2.Name = chtO1.Name & "_Grid"
chtO2.Left = chtO1.Left
chtO2.Top = chtO1.Top
 
' Format the new chart
With chtO2.Chart
    .PlotArea.Format.Fill.Visible = msoFalse
    .ChartArea.Format.Fill.Visible = msoFalse
    For Each ser In .SeriesCollection
        ser.Format.Fill.Visible = msoFalse
        ser.Format.Line.Visible = msoFalse
    Next ser
    .Axes(xlValue).MajorGridlines.Border.Color = RGB(255, 255, 255)
End With
End Sub