Show gridlines on top of series

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
679
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top