Verspohl_example

Excel: Multiple Lines Across X-Axis Categories

Lars Verspohl (@lverspohl) writes in:

I’ve got an issue that might have a hopefully simple solution, but if so, I can’t see it and couldn’t find an answer to it.

I have bar charts showing a time-series per x-axis category (across 12 weeks).

I would now like to show a second set of data organised in the same way (time-series across 12 weeks showing per x-axis category), however as a line-graph.

I’m not sure this is possible in the same chart, and I’m happy with this being in a second chart that I can overlay. Still, as I’m unsure as to how to go about this I guess my question is:

How do I organise a line-chart within each x-axis category? I appreciate that D3 can solve this elegantly, but unfortunately this needs to be in Excel for now.

Any other suggestions for better ways to visualize the data are certainly welcome.

(Excel data file)

21 thoughts on “Excel: Multiple Lines Across X-Axis Categories

  1. I think what you would like to do is possible in Excel. But why a bar chart for one variable over time and a line for another over time? Wouldn’t it be better to use 2 lines per country? And with an extended Y-axis, you could plot all lines above each other, which is much better for making comparisons between countries and variables.

    It also depends on what you want to show. If you want to show some kind of relation between the two variables, I think you should go for 9 small multiple scatterplots.

    On a side note: is this from an Excel template or from some Excel default? I came across a very similar graph in a Belgian newspaper some time ago and made a redesign of it (although my redesign would look a lot different if I would remake it now :-): http://maartenlambrechts.drupalgardens.com/content/alles-kan-beter-autoverkoop-europa

  2. This isn’t too hard if you don’t mind the individual lines living off of the original bar chart.

    You can create sparklines for each of the categories (countries) you have here and use them for your individual line charts for the data you want to represent. I’ve included a screenshot here to show you what I was able to quickly mockup.

    To make sparklines
    - highlight the data you want to visualize
    - click the charts tabs
    - choose “insert sparklines” and make sure to selet the lines option
    - it will ask you where to place the sparklines
    - choose nine cells on the same row (I used a row above the graph for my example)
    - edit the look of your lines.

  3. Many thanks for your comments on this !

    @Marteen and Jon: The bar variable represents actual figures, the line variable less important forecast figures. My thought was to present the actuals in a visually more dominant way. In addition, the latest figure is the most important one, which I can then also encode with the highest saturation (the saturation would need to be as in the chart you showed, Marteen). Oh and the similarity to your chart is eery, but coincidental…

    @Ernesto: that’s a great idea and would work well ! My only problem is that this chart will probably have to link into a pivot table (I should have clarified – apologies). Hence, the parallel alignment of sparklines and bar chart will disconnect when the user picks a different number of countries. Also, ideally the 2 charts would share the same scale.

    @Jon: very honoured re your post! eagerly awaiting it …

    Many thanks again !! Lars

  4. Here you go

    http://ow.ly/d/1Qlr

    Basically, I create two charts and overlayed one on top of the other

    The line chart is a single series, with a gap between each group

    The line chart has transparent background, plot area and the axes have been removed.

    It’s a kludge, but I think its the best way to do it.

  5. Another way

    http://ow.ly/d/1Qly

    this method keeps the data on single chart – this might be important – but not as visually the same

    - add the data series for the ‘line’ as a group colum chart
    - add a ‘plus’ error bar with fixed value of 0 & format colour
    - set data series as ‘no fill’

    Hope this helps

  6. Thanks _garilla – very good indeed ! Should’ve thought of option 1 really. Option 2 is even more elegant and will be very easy to implement in terms of data structure. Much obliged // +10

  7. Lars -

    I’m adding to my tutorial to show how to add the lines to a column chart. It’s easier than the two-chart approach you settled on. At least I find it easier, because there’s no need to make sure the axes on the top chart are aligned with those in the bottom chart.

    My personal preference if I am showing forecast and actual values is to show the forecast as the columns, and the actuals as the line. I use the same light shade for all of the forecast columns so they are less emphasized than the actuals, and I use a dark line for the actuals so they dominate the forecast values.

  8. Thanks Jon ! Very valuable input re actuals v forecast design and probably best for me to sit tight and wait for the tutorial …

    thanks again everyone

  9. Have read and implemented it ! Excel greatness .

    thanks again to you Jon and everyone who commented for your help…. very much appreciated

  10. I understand you want the actual values to be highlighted, and specifically the most recent actual value. I think this can be accomplished with both measures being a line, with the actual colored by the difference from the forecast, the forecast line in gray so it is subdued, and last actual value for each country marked with a big circle. I also added a sort by average actual value.

  11. There is a surprisingly simple solution for this. Start by creating a bar chart with both data sets along the x axis. With your mouse, select the bars belonging to the second data set. Then go to “edit chart type” and select line chart. This will change the second data set into a line on top of the bars belonging to the first data set.

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=""> <strike> <strong>