stacked column chart for two data sets - Excel

Question

I am new here, although I have been browsing the blog several times before.

I wonder if there is some way (also using VBA, if needed) to create a stacked column chart displaying two different data sets in MS Excel 2016.

Looking around, I saw the same question received a positive answer when working with Google Charts (here's the thread stacked column chart for two data sets - Google Charts)

Basically, I have two tables about 2 countries with monthly data per different years. Sample:

The output I want is to show years on the horizontal axis and having a country represented in a stacked column that piles up monthly data on the side of the column of the other country (like the chart made using Google Charts as explained in the the thread linked above.

The solution should look that sample:

I did not find an answer to that question, can you please provide some help on that?

Please also note that I have very basic skills in VBA, i.e. I can more or less understand a code when I read it, but I am not able to write one myself.

Thanks a lot to everybody

Answer 1

Just use the normal stacked bar chart, if you want to group the stacks then leave blank columns in your table:

With a bit of formatting you can get almost exactly what you have. The only part that would be challenging is getting the year labels as a secondary horizontal axis. To do this i just created another series of all 0s with the years as labels and added this as a secondary axis (add in a secondary horizontal axis as well) and then on the secondary vertical axis choose to have it 'cross the axis' at value 0. After a touch of formatting you can get exactly what you want:

No need for VBA and certainly no need for JavaScript!

Answer 2

I see you linked a stackoverflow question where they use javascript to create a chart with google charts. I think this language is the best way to create complex charts like yours.

You mentionned that your coding skills weren't good in vba, I don't know if it is the same with javascript but I'll try to describe my code the best I can.

Here is a working code I have written for you:

https://www.funfun.io/1/#/edit/5a73067991f44418b7b84218

So as you may see, to make this example I used an online editor with an embedded spreadsheet. I enter my data in it and I access it with my code by using a JSON file, it is the short file underneath the Settings:

{
    "data": "=A1:D27"
}

After I gain access to my data, I store it in local variables, you can see that in the script.js file. I wrote some comments so you know which variable is what. I go throw my data with $internal.data, it's where the data from the spreadsheet is stored from column A to D, line 1 to 27:

for (var i = 0; i < $internal.data.length; i++) // $internal.data.length = 27 in this example
{
  if (i == 0 || i == 14) {
    country.push($internal.data[i][0]);
    continue;
  }
  if (i > 0 && i < 13) {
    months.push($internal.data[i][0]); // months
    dataA.push(
      [
        parseInt($internal.data[i][1]), // value 2015 countryA
        parseInt($internal.data[i][2]), // value 2016 countryA
        parseInt($internal.data[i][3]) // value 2017 countryA
      ]);
  }
  if (i > 13 && i < 27) {
    dataB.push(
      [
        parseInt($internal.data[i][1]), // value 2015 countryB
        parseInt($internal.data[i][2]), // value 2016 countryB
        parseInt($internal.data[i][3])  // value 2017 countryB
      ]);
  }
}

After I stored my data correctly I can make my chart with a javascript library, I used Highcharts (pretty similar to google charts), it has a good documentation with lots of examples.

I put all the data and some few options in a series variable which uses the format of highcharts, like so:

 var series = [];
  // We enter the data we need in series
  for(var i = 0; i < months.length; i++) {
    series.push({
        name: months[i] + " " + country[0],
        type: 'column',
        color: Highcharts.getOptions().colors[i+7],
        data: dataA[i],
        stack: country[0] // country A
    });
    series.push({
        name: months[i] + " " + country[1],
        type: 'column',
        color: Highcharts.getOptions().colors[i+7],
        data: dataB[i],
      stack: country[1] //country B
    });        
  }

...
// we enter series in the highchart chart
    series: series

I differenciate the two countries by putting a different value in the stack. This variable allows me to create a stacked bar chart with multiple bars with the same axis label.

Once this is done You get a chart like this:

If you don't want to display all the months you can click on the labels of the months you don't want and it will disapear, like so:

Once you have build your chart you can load it in Excel by using an excel add-in called Funfun.You just have to paste the URL of the online editor in the add-in. Here is how it looks like with my example:

You can then save the chart in different format by clicking on the top right of the chart:

I hope this helps, if I wasn't clear in my explanation don't hesitate to comment :)

Disclosure : I’m a developer of Funfun.