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.

Let’s get to work…

First those gaps in the lines where there’s missing data? Ideally we would have collected a complete data-set in the first place but we’re not always in control of that. We could come up with our own interpolation algorithm using some appropriate method (in this case, for example, we could use data from a similar day to fill the gaps). In a lot of cases leaving them as gaps is the best option to avoid the reader drawing a incorrect conclusion from interpolated data. Here however, the slow rate of change of the car park fullness and the relative smallness of the gaps makes me confident to just use linear interpolation over the gaps.

We don’t have to do that ourselves, Excel will do it for us on the plot by right clicking on the plot, going to Select Data then click the Hidden and Empty Cells button and select the option “Connect data points with line”.


Let’s now tidy up the axes. We know that fullness is never going to go over 100% so we can fix the y-axis between 0 to 1. The x-axis is hours of the day so let’s just show a single day with hourly tick marks by setting min to 0, max to 1 and the major unit to 1/24 (0.041666667). Let’s remove those gridlines too.


Better. We don’t really care about the specifics of the other car parks, we’ve just put them in there to show that Cabot Circus has a different profile. So let’s keep them but put the focus on the data for Cabot Circus by greying out all the other lines and setting the line color to a color that stands out and looks nice…


Sidenote: I’m don’t like repetitive tasks so I wrote a quick macro to remove gridlines and grey out all the data lines for me. If you know how to use Macros, you can find it here. You just select your chart and run it.

Now let’s stop those other grey lines crossing over Cabot Circus and bring it to the front by putting it to the bottom of the list in the Right-click>Select Data menu using the little down arrow.


We don’t really need the legend anymore as it’s meaningless so we ditch that too and our final finishing touch is just to add some text to clarify what we’re looking at and to add a little text box to put our message right into the chart…


Much better compared to the original plot of colourful spaghetti!

We could drop this new graph into a PowerPoint or a report and the readers can see and understand our message straight away without having to work for it.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>