Macro for greying out lines in Excel

Here’s a quick VBA macro for removing axis gridlines and greying out all the lines in an Excel plot.

This relates to the post Drawing Better Graphs – Have Your Data Tell the Story

Sub GreyLines()

    For Each axs In ActiveChart.Axes
        axs.HasMajorGridlines = False
        axs.HasMinorGridlines = False
    For Each srs In ActiveChart.SeriesCollection
        srs.MarkerStyle = xlMarkerStyleNone
        'srs.MarkerForegroundColor = RGB(200, 200, 170)
        'srs.MarkerBackgroundColor = RGB(170, 170, 170)
        srs.Format.Line.ForeColor.RGB = RGB(215, 215, 215)
End Sub

Drawing Better Graphs – Have Your Data Tell the Story

Graphs are supposed to communicate data in a meaningful way. That’s the whole point. But too often people miss the opportunity to create graphs that effectively communicate the story they want to tell in a way that’s both easily readable and aesthetically pleasing. In this time of decreasing attention spans, quickly and effectively telling the story of your data has never been more important.

So let’s take an example narrative and look at how we can create a better graph to get our message across. I’ve got some nice data about the usage of Bristol car parks we can use as an example. I have a hunch that Cabot Circus car park (very large capacity and primarily used by shoppers) has a lot of spare capacity that could be better used during weekdays so here’s my message.

Cabot Circus car park is underutilised in the week and they should offer reduced rates to office workers Monday to Friday to monetise that spare capacity.

Let’s start by plotting all the car park data I have for Bristol and Bath car parks for a single weekday using Excel’s default line plot…


(Of course if I was actually making the case for this I’d be using averaged and max/min data rather than a single day but this post is about charting not data usage so bear with me)

A pretty messy plot. We can find Cabot Circus’s data in there but we have to work for it.
…click here to read the rest…